Обсуждение: Combining two SELECTs by same filters
Hi,
I've a table like:
=> SELECT dt FROM sales WHERE id = 2; dt
----------------------------2005-10-25 21:43:35.8700492005-10-25 21:43:36.2541222005-10-25 21:43:36.5911962005-10-25
21:43:36.8933312005-10-2521:43:37.2656712005-10-25 21:43:37.6881862005-10-25 22:25:35.2131712005-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.
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);
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 | 62005-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
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.)