Re: [SQL] Please advice on query optimization - Mailing list pgsql-sql
From | Muhannad Shubita |
---|---|
Subject | Re: [SQL] Please advice on query optimization |
Date | |
Msg-id | CAJQCA5FNag6FzqqDQZHG=L3q-NR1M0apvxiua1bohpNxAn1_Gg@mail.gmail.com Whole thread Raw |
In response to | Re: [SQL] Please advice on query optimization ("David G. Johnston" <david.g.johnston@gmail.com>) |
List | 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_employeeLEFT 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 likeSELECT DISTINCT ON (day, employee) day, employee, time, device FROM tbl ORDER BY day, employee, time ASC -- firstSELECT DISTINCT ON (day, employee) day, employee, time, device FROM tbl ORDER BY day, employee, time DESC -- lastDavid 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-in20/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_infrom bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;Best regards.İyi çalışmalar.Samed YILDIRIM20.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 keyemployee_id -- foreign key references employee tableday char(8) -- YY-MM-DDsign_in TIMESTAMPtotal_hours INTEGERI want to display details grouped by day & employee Id, for example:Day Employee Total-Hours First-Sign-In Last-Sign-In20/4 emp1 4 8:22 3:2521/4 emp1 7 9:00 4:1121/4 emp2 2 11:00 01:11I 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_namefrom 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-Inselect sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in LIMIT 1;--Last-Sign-Inselect 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 LOOPbut 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 overkillis there a better way to do this?Thanks.Regards,Muhannad
Regards,
Muhannad