agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: Muhannad Shubita <muhannadshubita@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Please advice on query optimization
Date: Thu, 20 Apr 2017 22:29:47 +0300
Message-ID: <485421492716587@web5m.yandex.ru> (raw)
In-Reply-To: <CAJQCA5H2+Mv=KpoRyAWy0hLg=5sHskB-yEhk_TGYUh4Yj4ziSQ@mail.gmail.com>
References: <CAJQCA5GrSczfLEhSgf+xV3xLQdUrnfsFSGrJ7yqCrfq2iPwt=g@mail.gmail.com>
<334521492708706@web22g.yandex.ru>
<CAJQCA5H2+Mv=KpoRyAWy0hLg=5sHskB-yEhk_TGYUh4Yj4ziSQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
view thread (7+ messages) latest in thread
Message-ID: <485421492716587@web5m.yandex.ru>
Permalink: ../485421492716587@web5m.yandex.ru/
Also on: postgresql.org/message-id/485421492716587@web5m.yandex.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: samed@reddoc.net, muhannadshubita@gmail.com
Subject: Re: Please advice on query optimization
In-Reply-To: <485421492716587@web5m.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox