agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: Muhannad Shubita <muhannadshubita@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Please advice on query optimization
Date: Thu, 20 Apr 2017 20:18:26 +0300
Message-ID: <334521492708706@web22g.yandex.ru> (raw)
In-Reply-To: <CAJQCA5GrSczfLEhSgf+xV3xLQdUrnfsFSGrJ7yqCrfq2iPwt=g@mail.gmail.com>
References: <CAJQCA5GrSczfLEhSgf+xV3xLQdUrnfsFSGrJ7yqCrfq2iPwt=g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
view thread (7+ messages) latest in thread
Message-ID: <334521492708706@web22g.yandex.ru>
Permalink: ../334521492708706@web22g.yandex.ru/
Also on: postgresql.org/message-id/334521492708706@web22g.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: <334521492708706@web22g.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