First Value Of Each Month In SQL
I have a database containing time stamped data values, with multiple samples per d开发者_开发知识库ay, i.e.
05/01/11 01:00:00 - 1.23
05/01/11 01:12:34 - 0.99
....
05/01/11 23:59:59 - 2.34
05/02/11 00:11:22 - 4.56
etc
I am trying to get a list of the first sample of each month. I have managed to get a list of every sample on the 1st of each month:
SELECT
"RecordTime", "FormattedValue"
FROM
CDBHistoric
WHERE
"Id" = 12345 AND EXTRACT (DAY FROM "RecordTime") = 1
This gives data like
03/01/11 00:00:01 - 1.23
03/01/11 00:12:34 - 0.99
....
04/01/11 00:00:34 - 2.34
04/01/11 00:11:22 - 4.56
but I have been unable to get the first value of each group.
Here's a solution for SQL Server. The idea is to use a correlated subquery to identify
the minimum RecordTime
for each year and month. It should be easy to translate the idea to your flavour of DBMS.
WITH Data AS (
SELECT CONVERT(DATETIME, '1-May-2011 01:00:00') AS RecordTime, 1.23 AS FormattedValue
UNION ALL SELECT CONVERT(DATETIME, '1-May-2011 01:12:34'), 0.99
UNION ALL SELECT CONVERT(DATETIME, '1-May-2011 23:59:59'), 2.34
UNION ALL SELECT CONVERT(DATETIME, '2-May-2011 00:11:22'), 4.56
)
SELECT D1.*
FROM Data D1
WHERE D1.RecordTime = (SELECT MIN(RecordTime)
FROM Data D2
WHERE YEAR(D2.RecordTime) = YEAR(D1.RecordTime)
AND MONTH(D2.RecordTime) = MONTH(D1.RecordTime))
Edit: If your dataset is large then joining on the subquery should be substantially faster, but might be harder to translate into your flavour of DBMS:
SELECT D1.*
FROM Data D1
INNER JOIN (
SELECT MIN(D2.RecordTime) AS FirstMonthlyRecordTime
FROM Data D2
GROUP BY YEAR(D2.RecordTime), MONTH(D2.RecordTime)
) AS SubQuery
ON D1.RecordTime = SubQuery.FirstMonthlyRecordTime
Edit 2: The following code is the Standard SQL-92 version of my first query above (tested on mySQL):
SELECT T1.RecordTime, T1.FormattedValue
FROM CDBHistoric T1
WHERE T1.RecordTime = (SELECT MIN(T2.RecordTime)
FROM CDBHistoric T2
WHERE EXTRACT(YEAR FROM T1.RecordTime) = EXTRACT(YEAR FROM T2.RecordTime)
AND EXTRACT(MONTH FROM T1.RecordTime) = EXTRACT(MONTH FROM T2.RecordTime))
精彩评论