Determining whether a month contains entries
I have a list of timestamped logs and I'd like a query to return 12 booleans say whether a certain month contains any logs, for each month of the year (starting from January), i.e.:
(True, False, False, True, False ..., True)
I currently have the following query, which will show me all months containing data:
SELECT DISTINCT(EXTRACT(MONTH FROM logdate)) AS month
FROM mytable
WHERE EXTRACT(YEAR FROM logdate) = '2009'
ORDER BY month;
The output of which is a list of months containing data, e.g.:
(1, 2, 5, 6, 12)开发者_Python百科
I just can't work out the next step - any pointers would be appreciated.
I am using PostgreSQL v8.4.2. Thanks.
Use generate_series():
SELECT
CASE
WHEN EXTRACT(MONTH FROM logdate) IS NULL THEN false
ELSE true
END,
y AS month
FROM
mytable RIGHT JOIN generate_series(1,12) AS x(y) ON EXTRACT(month FROM logdate) = y
WHERE
EXTRACT(YEAR FROM logdate) = '2009';
Move the WHERE to the JOIN condition:
SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM logdate) IS NULL THEN false
ELSE true
END,
y AS month
FROM
mytable
RIGHT JOIN
(SELECT generate_series(1,12)) AS x(y) ON ((EXTRACT(MONTH FROM logdate)) = y AND EXTRACT(YEAR FROM logdate) = '2009')
ORDER BY
month ASC;
The year condition only apply for matching records.
I'm not sure, if this is the most elegant solution, but it should work (tested on Postgres):
SELECT (CASE WHEN max(sub.jan)=1 THEN true ELSE false END) AS jan,
(CASE WHEN max(sub.feb)=1 THEN true ELSE false END) AS feb,
...
FROM
(
SELECT (CASE WHEN (EXTRACT(MONTH FROM logdate))=1 THEN 1 ELSE 0 END) AS jan,
(CASE WHEN (EXTRACT(MONTH FROM logdate))=2 THEN 1 ELSE 0 END) AS feb,
...
FROM mytable
WHERE EXTRACT(YEAR FROM logdate) = '2009'
) as sub
精彩评论