Re: [SQL] Sequential event query
Hi Steve, Am having difficulties (there is a chance I could be the only one) trying to see how the results you've listed under "I would want to get:" section can be generated from the information you have provided in your implicit problem statement. Please reconstruct your question, show simple and clear sample data along with desired results. For example where does the event_type, sequential_events fields get their values from given the sample data in a single field you've provided? Allan. Steve Crawford wrote: I have a table that includes the following columns: event_time timestamptz device_id integer event_type integer ... There are hundreds of unique device_ids, about ten event_types and millions of records in the table. Devices can run the gamut from idle to fully utilized so for any given time-period a device might have anywhere from zero to thousands of events. I am trying to concoct two queries. 1: Analysis query to determine the distribution of sequential event_types. For example, if the event_types, in chronological order, were: 1 3 1 4 4 5 4 2 2 2 4 4 7 4 4 I would want to get: event_type, sequential_events, occurrences 1,1,2 2,3,1 3,1,1 4,1,1 4,2,3 5,1,1 7,1,1 May be a select event_type,sequential_events, count(*) as occurances from blah group by ... 2: Listing of all devices where the most recent N events are all identical. As noted above, the varying load on the devices means that for device 1, the last N might be the last 2 minutes but for device 3 it might be a day or two. I am looking for a query that will list any device having no variation in the recent events. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] GROUP BY on a column which might exist in one of two tables
Hello, I could use some help figuring out a complex "GROUP BY". As a setup, let's say I have two tables which stores a single "hit" or "view" in a row hits hit_id partner_id views view_id partner_id There is of course a "partners" table with a "partner_id" column. My target result is more like partner_id total_views total_hits Where the totals are "counts" of the the rows in the hits and views tables. There should be no rows for partners without hits or views. My first attempt at the SQL for this GROUP COALESCE(hits.partner_id,views.partner_id) which didn't work. I'm stuck on what to try next. Thanks! Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP BY on a column which might exist in one of two tables
On Wed, Jun 25, 2008 at 09:50:18AM -0400, Mark Stosberg wrote: > hits > hit_id > partner_id > > views > view_id > partner_id > > There is of course a "partners" table with a "partner_id" column. > > My target result is more like > > partner_id > total_views > total_hits select coalesce(h.partner_id, v.partner_id) as partner_id, coalesce(v.count, 0) as total_views, coalesce(h.count, 0) as total_hits from (select partner_id, count(*) from hits group by partner_id) as h full outer join (select partner_id, count(*) from views group by partner_id) as v on h.partner_id = v.partner_id ; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP BY on a column which might exist in one of two tables
> select > coalesce(h.partner_id, v.partner_id) as partner_id, > coalesce(v.count, 0) as total_views, > coalesce(h.count, 0) as total_hits > from > (select partner_id, count(*) from hits group by partner_id) as h > full outer join > (select partner_id, count(*) from views group by partner_id) as v > on h.partner_id = v.partner_id > ; > That looks right. Thanks! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP BY on a column which might exist in one of two tables
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Where the totals are "counts" of the the rows in the hits and views > tables. There should be no rows for partners without hits or views. How about something like this?: SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits FROM ( SELECT partner_id, hit_id, NULL AS view_id FROM hits UNION ALL SELECT partner_id, NULL, view_id FROM views ) AS foo GROUP BY 1; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200806251019 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkhiU+8ACgkQvJuQZxSWSsgNiACgmrUWfTv1ZSiiexOKja64p1F8 1hYAn3i+tYoEOIs2NIcSrExlvoyfJE+X =ryrm -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sequential event query
Allan Kamau wrote: Hi Steve, Am having difficulties (there is a chance I could be the only one) trying to see how the results you've listed under "I would want to get:" section can be generated from the information you have provided in your implicit problem statement. Remember the events are listed chronologically. Stated verbosely: 1: Value 1 happened 1 sequential time 3: Value 3 happened 1 sequential time 1: Value 1 happened 1 sequential time 4: 4: Value 4 happened 2 sequential times 5: Value 5 happened 1 sequential time 4 : Value 4 happened 1 sequential time 2: 2: 2 : Value 2 happened 3 sequential times 4: 4 : Value 4 happened 2 sequential times 7 : Value 7 happened 1 sequential time 4 : Value 4 happened 1 sequential time So the summary is: Event type 1 appeared in a cluster of 1 event 2 times Event type 2 appeared in a cluster of 3 events 1 time Event type 3 appeared in a cluster of 1 event 1 time Event type 4 appeared in a cluster of 1 event 1 time Event type 4 appeared in a cluster of 2 events 3 times Event type 5 appeared in a cluster of 1 event 1 time Event type 7 appeared in a cluster of 1 event 1 time Given the summary table, I can then look at distributions of run-lengths for different event-types. If I know the distributions for a system operating normally, I can establish criteria to use for generating alerts and alarms. I have generated the above using external programs but if I can do so with a query, I would prefer to do so. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sequential event query
Steve Crawford wrote: Allan Kamau wrote: Hi Steve, Am having difficulties (there is a chance I could be the only one) trying to see how the results you've listed under "I would want to get:" section can be generated from the information you have provided in your implicit problem statement. Remember the events are listed chronologically. Stated verbosely: 1: Value 1 happened 1 sequential time 3: Value 3 happened 1 sequential time 1: Value 1 happened 1 sequential time 4: 4: Value 4 happened 2 sequential times ... And for completeness I suppose I should state that I am aware that a run of, say, 4 sequential events includes runs of 1, 2 and 3 events. I'm only interested in the longest run in order to set thresholds. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] GROUP BY on a column which might exist in one of two tables
On Wed, 2008-06-25 at 14:20 +, Greg Sabino Mullane wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > > Where the totals are "counts" of the the rows in the hits and views > > tables. There should be no rows for partners without hits or views. > > How about something like this?: > > SELECT partner_id, COUNT(view_id) AS total_views, COUNT(hit_id) AS total_hits > FROM ( > SELECT partner_id, hit_id, NULL AS view_id FROM hits > UNION ALL > SELECT partner_id, NULL, view_id FROM views > ) AS foo > GROUP BY 1; That was very helpful, Greg. My new design looks much like this. Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
