pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCombining two SELECTs by same filters
4+ messages / 4 participants
[nested] [flat]
* Combining two SELECTs by same filters
@ 2005-10-26 15:16 Volkan YAZICI <volkan.yazici@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Volkan YAZICI @ 2005-10-26 15:16 UTC (permalink / raw)
To: pgsql-sql
Hi,
I've a table like:
=> SELECT dt FROM sales WHERE id = 2;
dt
----------------------------
2005-10-25 21:43:35.870049
2005-10-25 21:43:36.254122
2005-10-25 21:43:36.591196
2005-10-25 21:43:36.893331
2005-10-25 21:43:37.265671
2005-10-25 21:43:37.688186
2005-10-25 22:25:35.213171
2005-10-25 22:25:36.32235
(8 rows)
And I want to collect the count of sales at hour = 21 and hour = 22.
For this purpose, I'm using below SELECT query:
=> SELECT
-> (SELECT count(id) FROM sales
-> WHERE id = 2
-> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
-> (SELECT count(id) FROM sales
-> WHERE id = 2
-> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
?column? | ?column?
----------+----------
6 | 2
(1 row)
Isn't it possible to combine these two SELECTs as one. Because one of
their filters are same: id = 2. I'm just trying to avoid making 2
scans with nearly same filters.
Regards.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining two SELECTs by same filters
@ 2005-10-26 15:59 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Volkan YAZICI <volkan.yazici@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2005-10-26 15:59 UTC (permalink / raw)
To: Volkan YAZICI <volkan.yazici@gmail.com>; +Cc: pgsql-sql
On Wed, 2005-10-26 at 10:16, Volkan YAZICI wrote:
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> ----------+----------
> 6 | 2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Do something like this:
select count(id)
from sales
where id=2 and
dt between 'firstdatehere' and 'lastdatehere'
group by date_trunc('hour', dt);
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining two SELECTs by same filters
@ 2005-10-26 16:15 Michael Fuhr <mike@fuhr.org>
parent: Volkan YAZICI <volkan.yazici@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-10-26 16:15 UTC (permalink / raw)
To: Volkan YAZICI <volkan.yazici@gmail.com>; +Cc: pgsql-sql
On Wed, Oct 26, 2005 at 06:16:13PM +0300, Volkan YAZICI wrote:
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> ----------+----------
> 6 | 2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one.
If you can accept multiple rows instead of multiple columns then
one way would be to group by the hour:
SELECT date_trunc('hour', dt) AS hour, count(*)
FROM sales
WHERE id = 2
AND date_trunc('hour', dt) IN ('2005-10-25 21:00:00', '2005-10-25 22:00:00')
GROUP BY hour
ORDER BY hour;
hour | count
---------------------+-------
2005-10-25 21:00:00 | 6
2005-10-25 22:00:00 | 2
(2 rows)
Here's another possibility, but I find it a bit ugly:
SELECT sum(CASE date_trunc('hour', dt)
WHEN '2005-10-25 21:00:00' THEN 1
ELSE 0
END) AS count1,
sum(CASE date_trunc('hour', dt)
WHEN '2005-10-25 22:00:00' THEN 1
ELSE 0
END) AS count2
FROM sales
WHERE id = 2;
count1 | count2
--------+--------
6 | 2
(1 row)
If you're looking for the fastest method then use EXPLAIN ANALYZE
on each to see what works best on your data set.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining two SELECTs by same filters
@ 2005-10-26 18:29 Bruno Wolff III <bruno@wolff.to>
parent: Volkan YAZICI <volkan.yazici@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2005-10-26 18:29 UTC (permalink / raw)
To: Volkan YAZICI <volkan.yazici@gmail.com>; +Cc: pgsql-sql
On Wed, Oct 26, 2005 at 18:16:13 +0300,
Volkan YAZICI <volkan.yazici@gmail.com> wrote:
> And I want to collect the count of sales at hour = 21 and hour = 22.
> For this purpose, I'm using below SELECT query:
>
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> ----------+----------
> 6 | 2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Use an OR clause when checking the time. You will need to enclose it
in parenthesis because AND binds tighter than OR.
For consecutive hours you could use a range test. (In fact you could use
a range test even for one hour and it might be fasterdepending on your
data and what indexes you have.)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-26 18:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 15:16 Combining two SELECTs by same filters Volkan YAZICI <volkan.yazici@gmail.com>
2005-10-26 15:59 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-26 16:15 ` Michael Fuhr <mike@fuhr.org>
2005-10-26 18:29 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox