[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Please advice on query optimization
From: Hector Vass <hector.vass () metametrics ! co ! uk>
Date: 2017-04-20 22:04:01
Message-ID: 92b4390f-9beb-4661-b9ba-b468dc42ccc0 () email ! android ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
or perhaps ...
select
day,
employee,
sum(total_hours),
max(case when r=1 then sign_in else null end) as first,
max(case when rr=1 then sign_in else null end) as last
from (
select
*,
row_number() over(partition by day,employee order by sign_in) as r,
row_number() over(partition by day,employee order by sign_in desc) as rr
from bioemployee
)x
group by day,employee
On 20 Apr 2017 9:09 p.m., Muhannad Shubita <muhannadshubita@gmail.com> wrote:
Thanks folks for your help, I will benchmark the suggested solutions & see what's the \
best fit here.
Awesome mailing list :)
On Thu, Apr 20, 2017 at 11:37 PM, David G. Johnston \
<david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>> wrote: Much easier to \
follow bottom-posting but I'll be consistent here.
It seems like you want a join of 4 sub-queries.
SELECT *
FROM unique_day_employee
LEFT JOIN aggregates_query USING (day, employee)
LEFT JOIN first_signin_query USING (day, employee)
LEFT JOIN last_signin_query USING (day, employee)
Where the definition of the first/last sign-in sub-queries are like
SELECT DISTINCT ON (day, employee) day, employee, time, device FROM tbl ORDER BY day, \
employee, time ASC -- first SELECT DISTINCT ON (day, employee) day, employee, time, \
device FROM tbl ORDER BY day, employee, time DESC -- last
David J.
On Thu, Apr 20, 2017 at 10:35 AM, Muhannad Shubita \
<muhannadshubita@gmail.com<mailto:muhannadshubita@gmail.com>> wrote: Thanks Samed,
one more question, what if I had other columns that cannot be used with an aggregate \
function (TEXT-based for example) but still needed to be paired with the first & last \
sign in? for instance, a randomly generated md5-ed ID by the sign in device that \
needs to be displayed as a reference:
Day Employee Total-Hours First-Sign-In device-id-of-first-sign-in Last-Sign-In \
device-id-of-last-sign-in
20/4 emp1 4 8:22 202cb962ac5.. 3:25 152d234b70..
On Thu, Apr 20, 2017 at 9:18 PM, Samed YILDIRIM \
<samed@reddoc.net<mailto:samed@reddoc.net>> wrote: Hi Muhannad,
Did you try using MIN and MAX function? I guess that following query solves your \
problem.
select employee_id, day, sum(total_hours) as total_hours, (select name from employee \
where id = employee_id) as emp_name, min(sign_in) as first_sign_in, max(sign_in) as \
last_sign_in from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day \
ORDER BY day;
Best regards.
İyi çalışmalar.
Samed YILDIRIM
20.04.2017, 19:56, "Muhannad Shubita" \
<muhannadshubita@gmail.com<mailto:muhannadshubita@gmail.com>>: Good day,
I have a table with biometric info about employees (let's call it bioemployee):
id serial not null -- primary key
employee_id -- foreign key references employee table
day char(8) -- YY-MM-DD
sign_in TIMESTAMP
total_hours INTEGER
I want to display details grouped by day & employee Id, for example:
Day Employee Total-Hours First-Sign-In Last-Sign-In
20/4 emp1 4 8:22 3:25
21/4 emp1 7 9:00 4:11
21/4 emp2 2 11:00 01:11
I created a pgsql function to get the details through the below query:
select employee_id, day, sum(total_hours) as total_hours, (select name from employee \
where id = employee_id) as emp_name from bioemployee where sign_in BETWEEN X and Y \
GROUP BY employee_id, day ORDER BY day;
now the problem is with getting First-Sign-In & Last-Sign-In per group (employee & \
day), I have currently implemented it in a FOR loop:
for RECORD in query LOOP
--First-Sign-In
select sign_in from bioemployee where employee_id = -- and day = -- and sign_in \
BETWEEN X and Y ORDER BY sign_in LIMIT 1;
--Last-Sign-In
select sign_in from bioemployee where employee_id = -- and day = -- and sign_in \
BETWEEN X and Y ORDER BY sign_in DESC LIMIT 1; return next \
json_build_object(first_sign_in, last_sign_in, ..rest of details); END LOOP
but If I had 100 employees over a span of 30 days, this would be 6000 queries inside \
the loop! which I am sure you would agree is an overkill
is there a better way to do this?
Thanks.
--
Regards,
Muhannad
--
Regards,
Muhannad
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<div dir="auto">
<div>or perhaps ...
<div dir="auto"><br>
</div>
<div dir="auto">select</div>
<div dir="auto"> day,</div>
<div dir="auto"> employee,</div>
<div dir="auto"> sum(total_hours),</div>
<div dir="auto"> max(case when r=1 then sign_in else null end) as first,</div>
<div dir="auto"> max(case when rr=1 then sign_in else null end) as last</div>
<div dir="auto">from (</div>
<div dir="auto"> select</div>
<div dir="auto"> *,</div>
<div dir="auto"> row_number() over(partition by \
day,employee order by sign_in) as r,</div> <div dir="auto"> \
row_number() over(partition by day,employee order by sign_in desc) as rr</div> \
<div dir="auto"> from bioemployee</div> <div dir="auto">)x</div>
<div dir="auto">group by day,employee</div>
<div dir="auto"><br>
</div>
<br>
<div class="gmail_extra"><br>
<div class="gmail_quote">On 20 Apr 2017 9:09 p.m., Muhannad Shubita \
<muhannadshubita@gmail.com> wrote:<br type="attribution"> <blockquote \
class="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> \
<div> <div dir="ltr">Thanks folks for your help, I will benchmark the suggested \
solutions & see what's the best fit here. <div><br>
</div>
<div>Awesome mailing list :)</div>
</div>
<div><br>
<div class="elided-text">On Thu, Apr 20, 2017 at 11:37 PM, David G. Johnston <span \
dir="ltr"> <<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>></span> \
wrote:<br> <blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <div dir="ltr">
<div style="font-family:'arial' , 'helvetica' , sans-serif">Much easier to follow \
bottom-posting but I'll be consistent here.</div> <div style="font-family:'arial' , \
'helvetica' , sans-serif"><br> </div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">It seems like you want a \
join of 4 sub-queries.</div> <div style="font-family:'arial' , 'helvetica' , \
sans-serif"><br> </div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">SELECT *</div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">FROM \
unique_day_employee</div> <div style="font-family:'arial' , 'helvetica' , \
sans-serif">LEFT JOIN aggregates_query USING (day, employee)</div> <div \
style="font-family:'arial' , 'helvetica' , sans-serif">LEFT JOIN first_signin_query \
USING (day, employee)</div> <div style="font-family:'arial' , 'helvetica' , \
sans-serif">LEFT JOIN last_signin_query USING (day, employee)</div> <div \
style="font-family:'arial' , 'helvetica' , sans-serif"><br> </div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">Where the definition of \
the first/last sign-in sub-queries are like</div> <div style="font-family:'arial' , \
'helvetica' , sans-serif"><br> </div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">SELECT DISTINCT ON (day, \
employee) day, employee, time, device FROM tbl ORDER BY day, employee, time ASC -- \
first</div> <div style="font-family:'arial' , 'helvetica' , sans-serif">SELECT \
DISTINCT ON (day, employee) day, employee, time, device FROM tbl ORDER BY day, \
employee, time DESC -- last<br> </div>
<div style="font-family:'arial' , 'helvetica' , sans-serif"><br>
</div>
<div style="font-family:'arial' , 'helvetica' , sans-serif">David J.</div>
<div style="font-family:'arial' , 'helvetica' , sans-serif"><br>
</div>
</div>
<div>
<div>
<div><br>
<div class="elided-text">On Thu, Apr 20, 2017 at 10:35 AM, Muhannad Shubita <span \
dir="ltr"> <<a href="mailto:muhannadshubita@gmail.com">muhannadshubita@gmail.com</a>></span> \
wrote:<br> <blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <div dir="ltr">Thanks Samed,
<div><br>
</div>
<div>one more question, what if I had other columns that cannot be used with an \
aggregate function (TEXT-based for example) but still needed to be paired with the \
first & last sign in? for instance, a randomly generated md5-ed ID by the sign in \
device that needs to be displayed as a reference:</div>
<div><br>
</div>
<div>
<div style="font-size:12.8px">Day <span style="white-space:pre-wrap"> \
</span>Employee<span style="white-space:pre-wrap"> </span>Total-Hours<span \
style="white-space:pre-wrap"> </span> First-Sign-In <span \
style="white-space:pre-wrap"> device-id-of-first-sign-in </span>Last-Sign-In \
device-id-of-last-sign-in</div> <div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">20/4<span style="white-space:pre-wrap"> \
</span>emp1<span style="white-space:pre-wrap"> </span>4<span \
style="white-space:pre-wrap"> </span>8:22<span style="white-space:pre-wrap"> \
202cb962ac5.. </span>3:25 \
152d234b70..</div> </div>
<div style="font-size:12.8px"><br>
</div>
<div>
<div>
<div><br>
<div class="elided-text">On Thu, Apr 20, 2017 at 9:18 PM, Samed YILDIRIM <span \
dir="ltr"> <<a href="mailto:samed@reddoc.net">samed@reddoc.net</a>></span> \
wrote:<br> <blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <div>Hi Muhannad,</div>
<div> </div>
<div>Did you try using MIN and MAX function? I guess that following query solves your \
problem.</div> <div> </div>
<div>
<div>select employee_id, day, sum(total_hours) as total_hours, (select name from \
employee where id = employee_id) as emp_name, <em><strong>min(sign_in) as \
first_sign_in, max(sign_in) as last_sign_in</strong></em></div> <div>from bioemployee \
where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div> \
<div> </div> <div>Best regards.</div>
</div>
<div> </div>
<div> </div>
<div><br>
</div>
<div><br>
</div>
<div>İyi çalışmalar.</div>
<div>Samed YILDIRIM</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>20.04.2017, 19:56, "Muhannad Shubita" <<a \
href="mailto:muhannadshubita@gmail.com">muhannadshubita@gmail.com</a>>:</div> \
<div> <div>
<blockquote>
<div dir="ltr">
<div>Good day,</div>
<div><br>
</div>
<div>I have a table with biometric info about employees (let's call it \
bioemployee):</div> <div><br>
</div>
<div>id serial not null -- primary key</div>
<div>employee_id -- foreign key references employee table</div>
<div>day char(8) -- YY-MM-DD</div>
<div>sign_in TIMESTAMP</div>
<div>total_hours INTEGER</div>
<div><br>
</div>
<div>I want to display details grouped by day & employee Id, for example:</div>
<div><br>
</div>
<div><br>
</div>
<div>Day <span style="white-space:pre-wrap"></span>Employee<span \
style="white-space:pre-wrap"> </span>Total-Hours<span style="white-space:pre-wrap"> \
</span>First-Sign-In <span style="white-space:pre-wrap"> </span>Last-Sign-In</div>
<div><br>
</div>
<div>20/4<span style="white-space:pre-wrap"> </span>emp1<span \
style="white-space:pre-wrap"> </span>4<span style="white-space:pre-wrap"> \
</span>8:22<span style="white-space:pre-wrap"> </span>3:25</div>
<div>21/4<span style="white-space:pre-wrap"> </span>emp1<span \
style="white-space:pre-wrap"> </span>7<span style="white-space:pre-wrap"> \
</span>9:00<span style="white-space:pre-wrap"> </span>4:11</div>
<div>21/4<span style="white-space:pre-wrap"> </span>emp2<span \
style="white-space:pre-wrap"> </span>2<span style="white-space:pre-wrap"> \
</span>11:00<span style="white-space:pre-wrap"> </span>01:11</div>
<div><br>
</div>
<div><br>
</div>
<div>I created a pgsql function to get the details through the below query:</div>
<div><br>
</div>
<div>select employee_id, day, sum(total_hours) as total_hours, (select name from \
employee where id = employee_id) as emp_name</div> <div>from bioemployee where \
sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div> <div><br>
</div>
<div>now the problem is with getting First-Sign-In & Last-Sign-In per \
group (employee & day), I have currently implemented it in a FOR loop:</div> \
<div><br> </div>
<div>for RECORD in query LOOP</div>
<div><span style="white-space:pre-wrap"></span>--First-Sign-In</div>
<div><span style="white-space:pre-wrap"></span>select sign_in from bioemployee where \
employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in LIMIT \
1;</div> <div><span style="white-space:pre-wrap"></span>--Last-Sign-In</div>
<div><span style="white-space:pre-wrap"></span>select sign_in from bioemployee where \
employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in DESC LIMIT \
1;</div> <div><span style="white-space:pre-wrap"></span></div>
<div><span style="white-space:pre-wrap"></span>return next \
json_build_object(first_sign_i<wbr>n, last_sign_in, ..rest of details);</div> \
<div>END LOOP</div> <div><br>
</div>
<div>but If I had 100 employees over a span of 30 days, this would be 6000 queries \
inside the loop! which I am sure you would agree is an overkill</div> <div><br>
</div>
<div><br>
</div>
<div>is there a better way to do this? </div>
<div><br>
</div>
<div><br>
</div>
<div>Thanks.</div>
</div>
</blockquote>
</div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
</div>
</div>
<font color="#888888">-- <br>
</font>
<div>
<div dir="ltr"><font face="monospace, monospace">Regards,</font>
<div><font face="monospace, monospace">Muhannad</font></div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<div>
<div dir="ltr"><font face="monospace, monospace">Regards,</font>
<div><font face="monospace, monospace">Muhannad</font></div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic