Обсуждение: SQL subqueries newbie help
Hi All, I need a little help regarding writing some subqueries. For example I have a table "items" which contains columns: itemid, description; and another table "events" with columns: itemid, date, event. events.itemid references items.itemid. Table events contains events regarding some itemid's from table items, so there could be multiple events regarding one item from items table. for example: Table: items itemid description 1 Lamp 2 Desk 3 HiFi Table: events itemid event date 1 purchase 2003-01-01 1 repair 2003-01-03 1 repair 2003-02-05 2 purchase 2003-02-01 3 HiFi 2003-02-01 I'd like to write a query which returns following information regarding each item: item, date of very first event, very last event.Is this possible? I think I can write several SELECT queries and procces them by an application or possibly write some procedure, but what is better solution? PS: are there any books regarding complex SQL queries in electronic format?
On Sat, Sep 06, 2003 at 14:07:09 +0300, Alexei Chetroi <lexoid@lexa.uniflux-line.net> wrote: > Hi All, > > I need a little help regarding writing some subqueries. For example I > have a table "items" which contains columns: itemid, description; and > another table "events" with columns: itemid, date, event. events.itemid > references items.itemid. Table events contains events regarding some > itemid's from table items, so there could be multiple events regarding > one item from items table. for example: > > Table: items > itemid description > 1 Lamp > 2 Desk > 3 HiFi > > Table: events > itemid event date > 1 purchase 2003-01-01 > 1 repair 2003-01-03 > 1 repair 2003-02-05 > 2 purchase 2003-02-01 > 3 HiFi 2003-02-01 > > I'd like to write a query which returns following information regarding > each item: item, date of very first event, very last event. > Is this possible? I think I can write several SELECT queries and > procces them by an application or possibly write some procedure, but > what is better solution? You can use group by to do this. If there were a large amount of events per item, you might get better performance by using disctinct on and limit in subqueries to get the max and min. That doesn't seem likely for this problem. Distinct on would also be useful if you want to get the event with the first and last dates. The query you want looks something like this: select description, min(date), max(date) from items, events where items.itemid = events.itemid group by description;
> I'd like to write a query which returns following information regarding
> each item: item, date of very first event, very last event.
> Is this possible? I think I can write several SELECT queries and
> procces them by an application or possibly write some procedure, but
> what is better solution?
Something like the below should do it:
SELECT item , min_date , min_event , max_date , max_event FROM items JOIN (SELECT min(date) AS min_date
, event AS min_event , item FROM events GROUP BY item) AS mn USING (item) JOIN (SELECT
max(date)AS max_date , event AS max_event , item FROM events GROUP BY item) AS mx
USING(item);
On Sat, Sep 06, 2003 at 01:21:36PM -0400, Rod Taylor wrote: > > I'd like to write a query which returns following information regarding > > each item: item, date of very first event, very last event. > > Is this possible? I think I can write several SELECT queries and > > procces them by an application or possibly write some procedure, but > > what is better solution? > > Something like the below should do it: > > SELECT item > , min_date > , min_event > , max_date > , max_event > FROM items > JOIN (SELECT min(date) AS min_date > , event AS min_event > , item > FROM events > GROUP BY item) AS mn USING (item) [skip] Thanks everybody for responses. I'm trying this one, but psql complains on queries like "SELECT min(date), event FROM events GROUP BY item" that events must be GROUPed or used in an aggregate function. Why this happens and why it needs be so?
On Sun, 2003-09-07 at 07:42, Alexei Chetroi wrote: > On Sat, Sep 06, 2003 at 01:21:36PM -0400, Rod Taylor wrote: > > > I'd like to write a query which returns following information regarding > > > each item: item, date of very first event, very last event. > > > Is this possible? I think I can write several SELECT queries and > > > procces them by an application or possibly write some procedure, but > > > what is better solution? > > > > Something like the below should do it: > > > > SELECT item > > , min_date > > , min_event > > , max_date > > , max_event > > FROM items > > JOIN (SELECT min(date) AS min_date > > , event AS min_event > > , item > > FROM events > > GROUP BY item) AS mn USING (item) > [skip] > > Thanks everybody for responses. I'm trying this one, but psql complains > on queries like "SELECT min(date), event FROM events GROUP BY item" that > events must be GROUPed or used in an aggregate function. Why this > happens and why it needs be so? Oh yeah, sorry. It needs to be like that because otherwise it is unsure what value to use for event. What you really want is a min function that runs a min on date, and returns the appropriate event -- which doesn't exist, but I believe could be created (see docs on Creating an Aggregate function if interested) In the mean time, find the event based on the dates and item id. This does assume that an item may only have one event per date. SELECT item , min_date , (SELECT event FROM events WHERE date = min_date AND item = items.item) , max_date , (SELECT event FROM events WHERE date = max_date AND item = items.item)FROM items JOIN (SELECT min(date) AS min_date , max(date) AS max_date , item FROMevents GROUP BY item) AS ev USING (item)
On Sun, Sep 07, 2003 at 09:09:35AM -0400, Rod Taylor wrote:> [skip] > > > > Thanks everybody for responses. I'm trying this one, but psql complains > > on queries like "SELECT min(date), event FROM events GROUP BY item" that > > events must be GROUPed or used in an aggregate function. Why this > > happens and why it needs be so? > > Oh yeah, sorry. It needs to be like that because otherwise it is unsure > what value to use for event. What you really want is a min functionI found it later. It's called vector aggregates > that runs a min on date, and returns the appropriate event -- which > doesn't exist, but I believe could be created (see docs on Creating an > Aggregate function if interested) > > In the mean time, find the event based on the dates and item id. This > does assume that an item may only have one event per date. I can use timestamp instead of date. Generally I see there more than one way to do it. I'm thinking of writing for example to functions which returns one row for the very first/last event based on item id. Currently I've achieved what I need using temporary tables, but I don't like it much. It seems to bee to ugle. Here what I did: SELECT date, event, item INTO TEMPORARY TABLE firstevent FROM events WHERE date IN (SELECT min(date) FROM eventsGROUP BY item); SELECT date, event, item INTO TEMPORARY TABLE lastevent FROM events WHERE date IN (SELECT max(date) FROM eventsGROUP BY item); SELECT item, f.date, f.event, l.date, l.event FROM items AS i JOIN firstrot AS f USING(item) JOIN lastrotAS l USING(item); I know I may use subselects instead of temporary tables in last query, but I don't know what performance impact I may face. Or should I add additional fields to items table and write a trigger procedure on events which updates these fields, or keep a two separate tables for the first/last event of each item and a trigger to update this tables on events change. I'm just learning and doubting about correct approach. Currently I'm considering writing a procedures which return row of the first/last event using item as key.
Rod Taylor <rbt@rbt.ca> writes:
> Oh yeah, sorry. It needs to be like that because otherwise it is unsure
> what value to use for event. What you really want is a min function
> that runs a min on date, and returns the appropriate event -- which
> doesn't exist, but I believe could be created
SELECT DISTINCT ON might provide a simpler solution to that requirement.
Check out the "weather reports" example in the SELECT reference page.
regards, tom lane