Re: [SQL] selecting records X minutes apart
On Fri, 3 Jun 2011 15:52:53 -0400 lists-pg...@useunix.net wrote: I also think you might want to use WITH RECURSIVE clause. This SQL searches the case of an interval of 5 minutes or more, and sets a relationship between a parent to its child. CREATE TABLE tbl(id integer, ts time) ; INSERT INTO tbl VALUES (0, '20:00'), (0, '20:05'), (0, '20:08'), (0, '20:10'), (0, '20:11'), (1, '20:03'), (1, '20:04'), (1, '20:05'), (1, '20:09'), (1, '20:16'); SELECT * FROM tbl; -- WITH RECURSIVE rec(id , ts_p, ts_c) AS ( SELECT a1.id, min(a1.ts), min(b1.ts) FROM tbl AS a1, tbl AS b1 WHERE a1.id=b1.id AND a1.ts + interval'5 minute' = b1.ts GROUP BY a1.id UNION ALL SELECT t2.id, t2.ts_p, t2.ts_c FROM rec AS t1 INNER JOIN (SELECT a2.id, a2.ts as ts_p, min(b2.ts) AS ts_c FROM tbl AS a2, tbl AS b2 WHERE a2.id = b2.id AND a2.ts + interval'5 minute' = b2.ts GROUP BY a2.id, a2.ts UNION ALL SELECT a3.id, a3.ts, null FROM tbl AS a3 ) AS t2 ON t1.id = t2.id AND t1.ts_c=t2.ts_p ) SELECT DISTINCT id, ts_p AS ts FROM rec ORDER BY 1,2; I have a table that, at a minimum, has ID and timestamp columns. Records are inserted into with random IDs and timestamps. Duplicate IDs are allowed. I want to select records grouped by ID, ordered by timestamp that are X minutes apart. In this case X is 5. Note, the intervals are not X minute wall clock intervals, they are X minute intervals from the last accepted record, per-id. For instance here is some sample input data: IDTS (HH:MM) --- 0 20:00 1 20:03 1 20:04 0 20:05 1 20:05 0 20:08 1 20:09 0 20:10 I'd want the select to return: IDTS (HH:MM) --- 0 20:00 0 20:05 0 20:10 1 20:03 1 20:09 Does my question make sense? Thanks in advance, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] selecting records X minutes apart
On Fri, 3 Jun 2011 15:52:53 -0400 lists-pg...@useunix.net wrote: I also think you might want to use WITH RECURSIVE clause. This SQL searches the case of an interval of 5 minutes or more, and sets a relationship between a parent to its child. CREATE TABLE tbl(id integer, ts time) ; INSERT INTO tbl VALUES (0, '20:00'), (0, '20:05'), (0, '20:08'), (0, '20:10'), (0, '20:11'), -- added as a sample. (1, '20:03'), (1, '20:04'), (1, '20:05'), (1, '20:09'), (1, '20:16'); -- added as a sample. SELECT * FROM tbl; -- WITH RECURSIVE rec(id , ts_p, ts_c) AS ( SELECT a1.id, min(a1.ts), min(b1.ts) FROM tbl AS a1, tbl AS b1 WHERE a1.id=b1.id AND a1.ts + interval'5 minute' = b1.ts GROUP BY a1.id UNION ALL SELECT t2.id, t2.ts_p, t2.ts_c FROM rec AS t1 INNER JOIN (SELECT a2.id, a2.ts as ts_p, min(b2.ts) AS ts_c FROM tbl AS a2, tbl AS b2 WHERE a2.id = b2.id AND a2.ts + interval'5 minute' = b2.ts GROUP BY a2.id, a2.ts UNION ALL SELECT a3.id, a3.ts, null FROM tbl AS a3 ) AS t2 ON t1.id = t2.id AND t1.ts_c=t2.ts_p ) SELECT DISTINCT id, ts_p AS ts FROM rec ORDER BY 1,2; Regards, Masaru Sugawara I have a table that, at a minimum, has ID and timestamp columns. Records are inserted into with random IDs and timestamps. Duplicate IDs are allowed. I want to select records grouped by ID, ordered by timestamp that are X minutes apart. In this case X is 5. Note, the intervals are not X minute wall clock intervals, they are X minute intervals from the last accepted record, per-id. For instance here is some sample input data: IDTS (HH:MM) --- 0 20:00 1 20:03 1 20:04 0 20:05 1 20:05 0 20:08 1 20:09 0 20:10 I'd want the select to return: IDTS (HH:MM) --- 0 20:00 0 20:05 0 20:10 1 20:03 1 20:09 Does my question make sense? Thanks in advance, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Subquery problems
On Thu, 21 Jun 2007 14:46:49 -0300 Ranieri Mazili [EMAIL PROTECTED] wrote: Hi, I noticed that if DIV_MES = 0 (= NULL) then the previous query didn't go well. I rewrite the query. It's including a check statement to replace NULL with 1. At first, you need to create a table of months. -- Masaru Sugawara create table tbl_month (p_month integer); insert into tbl_month values (1); insert into tbl_month values (2); insert into tbl_month values (3); insert into tbl_month values (4); insert into tbl_month values (5); insert into tbl_month values (6); insert into tbl_month values (7); insert into tbl_month values (8); insert into tbl_month values (9); insert into tbl_month values (10); insert into tbl_month values (11); insert into tbl_month values (12); select C.id_production_area, B.id_machine_type, A.h_month as month, max(A.n) as div_mes, cast((sum(A.qty_employees_total) -(sum(A.qty_absence) -sum(A.qty_vacation) -sum(A.qty_diseased) -sum(A.qty_indirect) -sum(A.qty_transferred)) +sum(A.qty_received))/max(A.n) as integer) from (select * from (select *, extract(month from h1.head_count_date) as h_month from head_count as h1 where extract(year from h1.head_count_date) = extract(year from current_date) ) as h2, (select t1.p_month, coalesce (p1.n, 1) as n from tbl_month as t1 left outer join (select extract(month from production_date) as m, count(distinct p0.production_date) as n from production as p0 where extract(year from production_date) = extract(year from current_date) group by extract(month from production_date) ) as p1 on (t1.p_month = p1.m) ) as p2 where h2.h_month = p2.p_month ) as A, machine B, machine_type C where A.id_machine = B.id_machine and B.id_machine_type = C.id_machine_type group by C.id_production_area, B.id_machine_type, A.h_month order by C.id_production_area, A.h_month, A.h_month DESC; Hello, Your solution works fine, I would appreciated your prompt reply. Thanks a lot Original Message Subject: Re:[SQL] Subquery problems From: Masaru Sugawara [EMAIL PROTECTED] To: Ranieri Mazili [EMAIL PROTECTED] Date: 21/6/2007 13:25 On Tue, 19 Jun 2007 09:17:22 -0300 Ranieri Mazili [EMAIL PROTECTED] wrote: Hi, This reply is not accurate, but I think there are helpful hints. -- Masaru Sugawara select C.id_production_area, B.id_machine_type, A.h_month as month, max(A.n) as div_mes, cast((sum(A.qty_employees_total) -(sum(A.qty_absence) -sum(A.qty_vacation) -sum(A.qty_diseased) -sum(A.qty_indirect) -sum(A.qty_transferred)) +sum(A.qty_received))/max(A.n) as integer) from (select * from (select *, extract(month from h1.head_count_date) as h_month from head_count as h1 where extract(year from h1.head_count_date) = extract(year from current_date) ) as h2, (select extract(month from production_date) as p_month, count(distinct p1.production_date) as n from production as p1 where extract(year from production_date) = extract(year from current_date) group by extract(month from production_date) ) as p2 where h2.h_month = p2.p_month ) as A, machine B, machine_type C where A.id_machine = B.id_machine AND B.id_machine_type = C.id_machine_type group by C.id_production_area, B.id_machine_type, A.h_month order by C.id_production_area, A.h_month, A.h_month DESC; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Subquery problems
On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili [EMAIL PROTECTED] wrote:
Hi,
This reply is not accurate, but I think there are helpful hints.
--
Masaru Sugawara
select
C.id_production_area,
B.id_machine_type,
A.h_month as month,
max(A.n) as div_mes,
cast((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/max(A.n) as integer)
from (select *
from
(select *, extract(month from h1.head_count_date) as h_month
from head_count as h1
where extract(year from h1.head_count_date)
= extract(year from current_date)
) as h2,
(select extract(month from production_date) as p_month,
count(distinct p1.production_date) as n
from production as p1
where extract(year from production_date)
= extract(year from current_date)
group by extract(month from production_date)
) as p2
where h2.h_month = p2.p_month
) as A, machine B, machine_type C
where A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
group by C.id_production_area, B.id_machine_type, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;
Hello,
I'm having another problem, I have a function that declares 12
variable, one per month and each them execute a select like bellow:
*DIV_MES01 := (select count(distinct production_date) from production
where extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
*Then, I need to check if the variable is equal 0:
*IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
*Finally, I perform the following query:
*SELECTcast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
*Doing it, I need to perform 12 querys united by UNION, what I want to
do is unify it in only one query, I tryed with the query bellow:
*SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct production_date)
from production
whereextract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHEREdate_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
*But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] query optimization question
On Wed, 6 Nov 2002 09:01:49 -0500
[EMAIL PROTECTED] wrote:
If anyone can see a way to do a group by to do this, then I will be happy to
hear about it, because currently the resultset has to do a separate
(sequential or index) scan of the deficiencies table. The only way I can
see to do a group by would be to break out the aging categories into
separate queries, but that wins me nothing because each query then does its
own scan...
The expected simplified output of this query looks like this:
Project 30 30-60 =60lot total 30 30-60 =60def
total
X 1 2 1 4 5 10 5 20 (if X had 4
lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3
deficiencies in project Y)
The following query may be one of the ways, but I cannot confirm whether
it goes well or not.
SELECT
project_id,
marketing_name,
COUNT(lots.lot_id) AS def_count,
COUNT(CASE WHEN dt.days_old_start_date {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_less_30,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-10-07'}
AND dt.days_old_start_date {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_30_60,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-09-07'}
AND dt.days_old_start_date {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_60_90,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_greater_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_less_30,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-10-07'}
AND dt.days_old_start_date {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_30_60,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-09-07'}
AND dt.days_old_start_date {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_60_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_greater_90,
COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
(SELECT * FROM deficiency_table
WHERE assigned_supplier_id = '101690') AS dt,
(SELECT * FROM deficiency_status,
WHERE ds.is_outstanding) AS ds,
(SELECT * FROM projects
WHERE proj.division_id = 'GGH') AS proj
lots
WHERE
dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND NOT EXISTS
(SELECT 1 FROM menu_group_projects
WHERE menu_code = 'WA'
AND division_id = proj.division_id
AND project_id = proj.project_id
AND status = 'I')
ORDER BY proj.project_id
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query optimization question
On Thu, 07 Nov 2002 01:44:25 +0900
I wrote [EMAIL PROTECTED] wrote:
On Wed, 6 Nov 2002 09:01:49 -0500
[EMAIL PROTECTED] wrote:
If anyone can see a way to do a group by to do this, then I will be happy to
hear about it, because currently the resultset has to do a separate
(sequential or index) scan of the deficiencies table. The only way I can
see to do a group by would be to break out the aging categories into
separate queries, but that wins me nothing because each query then does its
own scan...
The expected simplified output of this query looks like this:
Project 30 30-60 =60lot total 30 30-60 =60def
total
X 1 2 1 4 5 10 5 20 (if X had 4
lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3
deficiencies in project Y)
The following query may be one of the ways, but I cannot confirm whether
it goes well or not.
There are some misspelling in FROM clause. Now they are fixed.
SELECT
project_id,
marketing_name,
COUNT(lots.lot_id) AS def_count,
COUNT(CASE WHEN dt.days_old_start_date {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_less_30,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-10-07'}
AND dt.days_old_start_date {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END) AS def_count_30_60,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-09-07'}
AND dt.days_old_start_date {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_60_90,
COUNT(CASE WHEN dt.days_old_start_date = {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END) AS def_count_greater_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date {d '2002-10-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_less_30,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-10-07'}
AND dt.days_old_start_date {d '2002-09-07'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_30_60,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-09-07'}
AND dt.days_old_start_date {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_60_90,
COUNT(DISTINCT(CASE WHEN
dt.days_old_start_date = {d '2002-08-08'}
THEN lots.lot_id ELSE NULL END )) AS lot_count_greater_90,
COUNT(DISTINCT lots.lot_id) AS lot_count
FROM
(SELECT * FROM deficiency_table
WHERE assigned_supplier_id = '101690') AS dt,
(SELECT * FROM deficiency_status
WHERE is_outstanding) AS ds,
(SELECT * FROM projects
WHERE division_id = 'GGH') AS proj,
lots
WHERE
dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND dt.deficiency_status_id = ds.deficiency_status_id
AND NOT EXISTS
(SELECT 1 FROM menu_group_projects
WHERE menu_code = 'WA'
AND division_id = proj.division_id
AND project_id = proj.project_id
AND status = 'I')
ORDER BY proj.project_id
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 09:10:53 +0200 Andre Schubert [EMAIL PROTECTED] wrote: The ride side as follows. Table d contains information about subitems. Table c holds information about subitems and items of type bar. Each subitem can have 0..n items bar selected. What i want is that a subitem is only activated for a foo item if the foo-item has exactly selected the same bar items selected as as the relation between table c and d. Example 1: The foo-item A_Name1 has selected the bar-items 1 and 2. The subitem D_Name1 is only activated for a foo-item if that foo-item has selected the bar-items 1 and 2, this happens for A_Name1. If this mention implies that the tuple of (1, 1) in the c is supposed to take precedence over that of (2, 1), SELECT a.name, d.name FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id FROM b, c WHERE b.c_id = c.b_id GROUP BY b.c_id, c.d_id ) AS t, a, d WHERE a.id = t.a_id AND d.id = t.d_id Regards, Masaru Sugawara ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] parser: parse error at or near $1
On Wed, 14 Aug 2002 17:56:16 +0530 Sugandha Shah [EMAIL PROTECTED] wrote: -- check for the exact same status string reported -- previously and skip it if found select into last_status top 1 status, last_code status_code from status_log where schedule_id =schedule_id order by complete_time desc ; PostgreSQL doesn't have a TOP keyword. You'll need to use a LIMIT keyword. See docs. http://www.postgresql.org/idocs/index.php?sql-select.html Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 16:04:21 +0200 Andre Schubert [EMAIL PROTECTED] wrote: If this mention implies that the tuple of (1, 1) in the c is supposed ^^ b to take precedence over that of (2, 1), I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. In c exists 3 tuples: (1,2), (3,4), (5) and want to find these tuples in b. I were able to catch what conditions join b to c. I try to think again. select a.name,d.name from a,b,c,d where tuple found in b = tuple found in c and b.a_id = a.id and c.d_id = d.id My english is not so good, but i hope you understand what i want to do. Thanks in advance SELECT a.name, d.name FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id FROM b, c WHERE b.c_id = c.b_id GROUP BY b.c_id, c.d_id ) AS t, a, d WHERE a.id = t.a_id AND d.id = t.d_id Regards, Masaru Sugawara ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] please help on query
On Tue, 16 Jul 2002 10:51:03 +0200 Luis Alberto Amigo Navarro [EMAIL PROTECTED] wrote: Aggregate (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1236941.71..1454824.56 rows=62 loops=1) - Group (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual time=1233968.87..1385034.91 rows=6001225 loops=1) - Sort (cost=1257368.92..1257368.92 rows=6001225 width=12) (actual time=1233968.82..1276147.37 rows=6001225 loops=1) - Hash Join (cost=166395.00..520604.08 rows=6001225 width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1) - Seq Scan on lineitem (cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1) - Hash (cost=162645.00..162645.00 rows=150 width=4) (actual time=59032.16..59032.16 rows=0 loops=1) - Seq Scan on orders (cost=0.00..162645.00 rows=150 width=4) (actual time=17.33..44420.10 rows=150 loops=1) Total runtime: 1454929.11 msec Hmm, does each of the three tables have some indices like the following? If not so, could you execute EXPLAIN ANALYZE after creating the indices. create index idx_lineitem_orderkey on lineitem(orderkey); create index idx_orders_orderkey on orders(orderkey); create index idx_orders_custkey on orders(custkey); create index idx_customer_custkey on customer(custkey); Regards, Masaru Sugawara ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] please help on query
On Mon, 15 Jul 2002 09:45:36 +0200 Luis Alberto Amigo Navarro [EMAIL PROTECTED] wrote: This is the output: Aggregate (cost=0.00..647161.10 rows=600122 width=8) (actual time=4959.19..347328.83 rows=62 loops=1) - Group (cost=0.00..632158.04 rows=6001225 width=8) (actual time=10.79..274259.16 rows=6001225 loops=1) - Index Scan using lineitem_pkey on lineitem (cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11 rows=6001225 loops=1) Total runtime: 347330.28 msec it is returning all rows in lineitem. Why is it using index? Sorry, I don't know the reason. I need more info. Can you show me the outputs of EXPLAIN ANALYZE ? EXPLAIN ANALYZE SELECT orders.orderkey FROM lineitem LEFT OUTER JOIN orders USING(orderkey) WHERE orders.orderkey IS NOT NULL GROUP BY orders.orderkey HAVING SUM(lineitem.quantity) 300; EXPLAIN ANALYZE SELECT t2.* FROM (SELECT orders.orderkey FROM lineitem LEFT OUTER JOIN orders USING(orderkey) WHERE orders.orderkey IS NOT NULL GROUP BY orders.orderkey HAVING SUM(lineitem.quantity) 300 ) AS t1 LEFT OUTER JOIN orders AS t2 USING(orderkey) ORDER BY t2.custkey Regards, Masaru Sugawara ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] please help on query
On Fri, 12 Jul 2002 17:32:50 +0200 Luis Alberto Amigo Navarro [EMAIL PROTECTED] wrote: Lineitem is being modified on run time, so creating a temp table don't solves my problem The time of creating this table is the same of performing the subselect (or so I think), it could be done creating a new table, and a new trigger, but there are already triggers to calculate lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco unt) and to calculate orderstatus in order with linestatus and to calculate orders.totalprice as sum(extendedprice) where lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if sum(quantity) where orderkey=new.orderkey might be excessive. Any other idea? Thanks And Regards - Original Message - From: Jakub Ouhrabka [EMAIL PROTECTED] To: Luis Alberto Amigo Navarro [EMAIL PROTECTED] Cc: Manfred Koizar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 12, 2002 1:50 PM Subject: Re: [SQL] [HACKERS] please help on query avoid subselect: create a temp table and use join... CREATE TEMP TABLE tmp AS SELECT lineitem.orderkey FROM lineitem WHERE lineitem.orderkey=orders.orderkey GROUP BY lineitem.orderkey HAVING sum(lineitem.quantity)300; Hi, I'm not sure whether its performance can be improved or not. But I feel there is a slight chance to reduce the total number of the tuples which Planner must think. BTW, how much time does the following query take in your situation, and how many rows does it retrieve ? EXPLAIN ANALYZE SELECT lineitem.orderkey FROM lineitem GROUP BY lineitem.orderkey HAVING SUM(lineitem.quantity) 300; Regards, Masaru Sugawara ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 2 Selects 1 is faster, why?
On Wed, 26 Jun 2002 17:34:47 +0200 Alvar Freude [EMAIL PROTECTED] wrote: Hi, -- Eric [EMAIL PROTECTED] wrote: If I perform the following 2 selects, the first one is EXTREMELY slow where the 2nd one is very fast. [...] Why would 2 be so much faster? I have ran the EXPLAIN on this and index scans are being used. I guess, the first query has to search for all ol.orderid the equivalent o.orderid; the second variant only has to search for '1234' in each ?.orderid, which is much faster. You are right. And this type of optimising are not yet implemented. Tom said it in the prior discussions. Regards, Masaru Sugawara ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Calculating with sql
On Wed, 26 Jun 2002 22:18:07 +0200 Dennis Kaarsemaker [EMAIL PROTECTED] wrote: From a copy of the CDDB-database i want to select the artist album that has on average the longest tracks. But in the tracks table there is no such field as length, so i have to calculate it. But when i try to do so it just gives me errors. This is the erroneous query i have made so far select albums.ARTIST, albums.TITLE from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID having(albums.LENGTH/count(tracks.TRACK)) = ( select max(albums.LENGTH/count(tracks.TRACK)) from tracks,albums where tracks.DISCID = albums.DISCID group by tracks.DISCID ); What is the correct way of selecting the album? As for this query, some columns, AERIST, TITLE, and LENGTH, are lacking at the GROUP BY clauses. Probably, I would think a nearly correct one is something like the following. SELECT a1.ARTIST, a1.TITLE FROM (SELECT a0.DISCID, a0.ARTIST, a0.TITLE FROM tracks AS t0, albums AS a0 WHERE t0.DISCID = a0.DISCID GROUP BY a0.DISCID, a0.ARTIST, a0.TITLE , a0.LENGTH HAVING a0.LENGTH /COUNT(t0.TRACK) = (SELECT max(mx.average) FROM (SELECT albums.DISCID, albums.LENGTH /COUNT(tracks.TRACK) AS average FROM tracks, albums WHERE tracks.DISCID = albums.DISCID GROUP BY albums.DISCID, albums.LENGTH ) AS mx ) ) AS a1 Regards, Masaru Sugawara ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] MEDIAN as custom aggregate?
On Fri, 12 Oct 2001 12:38:12 -0700
Josh Berkus wrote:
For those whose stats terminology is rusty, the median is the middle
value in a distribution. For example, if we had the following data:
Table ages
personage
Jim 21
Rusty 24
Carol 37
Bob 62
Leah 78
Our Median would be Carol's age, 37. This is a different figure from
the Mean, or Average, which is 44.4. Using the combination of the Mean
and the Median you can do all kinds of interesting statistical analysis.
In such case of this, there needs nextval in a query to deal with
a large number of rows. I think the following query, is not smart,
will return the necessary rows (or an average of the rows). But even
so it may need considerable time...
-- (on 7.1.3)
create sequence seq_ages start 1;
select a1.age, a1.rank -- or select avg(a1.age)
from (select a0.person, a0.age, (nextval('seq_ages') - 1) as rank
from (select *, setval('seq_ages',1) -- to reset a sequence
from ages
order by age -- this insignificant order by is
-- needed in order to work setval
) as a0
order by a0.age
) as a1
where exists (select * from ages
where a1.rank = (select (count(*)+1)/2 from ages)
and a1.rank = (select count(*)/2+1 from ages)
)
;
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] challenging query
On Fri, 05 Oct 2001 17:03:41 METDST Haller Christoph wrote: Consider the following table: A B C D select? --- 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y 3 FOO A1 102 y 4 BAR Z2 99 y 5 FOO A1 99 n 6 BAR Z2 98 n 7 FOO AB 103 y 7 BAR ZY 103 y This table has the idea of groups, that is, a group is defined as all of the words from B that have the same number A. The values in column C also matter- we want to select both groups A=7 and A=1 since they contain different values C. Note that the groups defined by A=1 and A=3 are distinct- they do not contain the same number of words from B, so we want to select them both. Also note that D is datetime, and all the rows with the same number A will have the same D (this is actually ensured by a single row in another table.) I want to select all of the numbers A which define distinct groups and have the highest datetime D. Is this possible in a SQL query? Now that I've read your request more attentively, I understand what you want. But I have to admit I have no idea how to word the query, I even don't know if it's possible at all. Regards, Christoph I also haven't satisfactorily understood the mean of the epilogue, but I probably think he wanted to account for the following table which is separated into groups. A B C D select? 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y - 5 FOO A1 99 n 3 FOO A1 102 y - 6 BAR Z2 98 n 4 BAR Z2 99 y - 7 FOO AB 103 y 7 BAR ZY 103 y for instance: select u0.A, u0.B, u0.C, u0.D from (select t0.*, t1.cnt from (select a, count(*) as cnt from test_table group by a ) as t1 inner join test_table as t0 on(t0.a = t1.a) ) as u0 where not exists (select u1.* from (select t0.*, t1.cnt from (select a, count(*) as cnt from test_table group by a ) as t1 inner join test_table as t0 on(t0.a = t1.a) ) as u1 where u1.cnt= u0.cnt and u1.a != u0.a and u1.d u0.d and u1.b = u0.b and u1.c = u0.c ) ; a | b | c | d ---+-++- 2 | BAR | Z2 | 101 2 | FOO | A1 | 101 3 | FOO | A1 | 102 4 | BAR | Z2 | 99 7 | BAR | ZY | 105 7 | FOO | AB | 105 (6 rows) Have a nice weekend! -- Masaru Sugawara ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL]plpgsql function case statement
Peter Schmidt wrote: I'm having trouble finding the best solution for the following plpgsql function. The function does the following: Hi, I post in this mailing list for the first time. :-) I think your pl/pgsql function runs properly, but there is likely to exist another SQL pattern. In this case, estimation of whether the TIMESTAMP value or NULL is finished at one time. Please try it, if you have time for checking. (on v7.1.3) create function updateLastUsed(text, text) returns integer as ' declare wm integer; rows_updated integer; begin rows_updated := 0; wm := getHighmark($1,$2); -- UPDATE info SET lastused = (SELECT case when MAX(p.requesttime) is null then info.firstused else MAX(p.requesttime) end FROM usage_log p WHERE p.id = info.id AND p.seq_no = wm ) -- return rows_updated; end; ' language 'plpgsql'; M.Sugawara [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
