agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPlease advice on query optimization
7+ messages / 4 participants
[nested] [flat]
* Please advice on query optimization
@ 2017-04-20 16:43 Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 7+ messages in thread
From: Muhannad Shubita @ 2017-04-20 16:43 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
@ 2017-04-20 17:18 ` Samed YILDIRIM <samed@reddoc.net>
2017-04-20 17:35 ` Re: Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Samed YILDIRIM @ 2017-04-20 17:18 UTC (permalink / raw)
To: Muhannad Shubita <muhannadshubita@gmail.com>; pgsql-sql
<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" <muhannadshubita@gmail.com>:</div><blockquote type="cite"><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;"> </span>Employee<span style="white-space:pre;"> </span>Total-Hours<span style="white-space:pre;"> </span> First-Sign-In <span style="white-space:pre;"> </span>Last-Sign-In</div><div><br /></div><div>20/4<span style="white-space:pre;"> </span>emp1<span style="white-space:pre;"> </span>4<span style="white-space:pre;"> </span>8:22<span style="white-space:pre;"> </span>3:25</div><div>21/4<span style="white-space:pre;"> </span>emp1<span style="white-space:pre;"> </span>7<span style="white-space:pre;"> </span>9:00<span style="white-space:pre;"> </span>4:11</div><div>21/4<span style="white-space:pre;"> </span>emp2<span style="white-space:pre;"> </span>2<span style="white-space:pre;"> </span>11:00<span style="white-space:pre;"> </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;"> </span>--First-Sign-In</div><div><span style="white-space:pre;"> </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;"> </span>--Last-Sign-In</div><div><span style="white-space:pre;"> </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;"> </span></div><div><span style="white-space:pre;"> </span>return next json_build_object(first_sign_in, 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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
@ 2017-04-20 17:35 ` Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 19:29 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
2017-04-20 19:37 ` Re: Please advice on query optimization David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Muhannad Shubita @ 2017-04-20 17:35 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: pgsql-sql
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> 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>:
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
2017-04-20 17:35 ` Re: Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
@ 2017-04-20 19:29 ` Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 7+ messages in thread
From: Samed YILDIRIM @ 2017-04-20 19:29 UTC (permalink / raw)
To: Muhannad Shubita <muhannadshubita@gmail.com>; +Cc: pgsql-sql
<div>Hi Muhannad,</div><div> </div><div>first_value and last_value functions can be used for these purpose. But query should be rewrited as below. Also for this case I'm not sure which one is better, sub query or window functions.</div><div> </div><div>SELECT DISTINCT employee_id, day, sum(total_hours) over w1, min(sign_in) OVER w1 as first_sign_in, max(sign_in) OVER w1 as last_sign_in, first_value(device_id) OVER w2 as first_sign_in_device, first_value(device_id) OVER w3 as last_sign_in_device</div><div><div>FROM <span>bioemployee </span></div><div><span>WHERE sign_in BETWEEN X and Y</span></div><div>WINDOW w1 as (PARTITION BY employee_id,day),w2 as (PARTITION BY employee_id,day ORDER BY sign_in ASC),w3 as (PARTITION BY employee_id,day ORDER BY sign_in DESC)</div><div>ORDER BY day;</div></div><div> </div><div>Best regards.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div>20.04.2017, 20:35, "Muhannad Shubita" <muhannadshubita@gmail.com>:</div><blockquote type="cite"><div>Thanks Samed,<div> </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> </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;"> </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;"> </div><div> <div>On Thu, Apr 20, 2017 at 9:18 PM, Samed YILDIRIM <span><<a target="_blank" href="mailto:samed@reddoc.net">samed@reddoc.net</a>></span> wrote:<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><span>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</span></div><div><span> </span></div><div>Best regards.</div></div><div> </div><div> </div><div> </div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>20.04.2017, 19:56, "Muhannad Shubita" <<a target="_blank" href="mailto:muhannadshubita@gmail.com">muhannadshubita@gmail.com</a>>:</div><div><div><blockquote type="cite"><div><div>Good day,</div><div> </div><div>I have a table with biometric info about employees (let's call it bioemployee):</div><div> </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> </div><div>I want to display details grouped by day & employee Id, for example:</div><div> </div><div> </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> </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> </div><div> </div><div>I created a pgsql function to get the details through the below query:</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</div><div>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div><div> </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> </div><div>for RECORD in query LOOP</div><div>--First-Sign-In</div><div>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>--Last-Sign-In</div><div>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> </div><div>return next json_build_object(first_sign_in, last_sign_in, ..rest of details);</div><div>END LOOP</div><div> </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> </div><div> </div><div>is there a better way to do this? </div><div> </div><div> </div><div>Thanks.</div></div></blockquote></div></div></blockquote></div> <div> </div>--<div><div><font face="monospace, monospace">Regards,</font><div><font face="monospace, monospace">Muhannad</font></div></div></div></div></div></blockquote>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
2017-04-20 17:35 ` Re: Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
@ 2017-04-20 19:37 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-20 20:07 ` Re: Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2017-04-20 19:37 UTC (permalink / raw)
To: Muhannad Shubita <muhannadshubita@gmail.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-sql
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> 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> 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>:
>>
>> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Re: Please advice on query optimization Samed YILDIRIM <samed@reddoc.net>
2017-04-20 17:35 ` Re: Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 19:37 ` Re: Please advice on query optimization David G. Johnston <david.g.johnston@gmail.com>
@ 2017-04-20 20:07 ` Muhannad Shubita <muhannadshubita@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Muhannad Shubita @ 2017-04-20 20:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-sql
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> 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> 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> 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>:
>>>
>>> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Please advice on query optimization
@ 2017-04-20 22:04 Hector Vass <hector.vass@metametrics.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Hector Vass @ 2017-04-20 22:04 UTC (permalink / raw)
To: Muhannad Shubita <muhannadshubita@gmail.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-sql; David G. Johnston <david.g.johnston@gmail.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-04-20 22:04 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-20 16:43 Please advice on query optimization Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 17:18 ` Samed YILDIRIM <samed@reddoc.net>
2017-04-20 17:35 ` Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 19:29 ` Samed YILDIRIM <samed@reddoc.net>
2017-04-20 19:37 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-20 20:07 ` Muhannad Shubita <muhannadshubita@gmail.com>
2017-04-20 22:04 Re: Please advice on query optimization Hector Vass <hector.vass@metametrics.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox