SQL 中每个月的第一个值

发布于 2024-11-05 09:11:34 字数 602 浏览 0 评论 0原文

我有一个包含时间戳数据值的数据库，每天有多个样本，即

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

我

试图获取每个月第一个样本的列表。我设法在每个月的 1 号获得每个样本的列表：

SELECT
    "RecordTime", "FormattedValue"
FROM
    CDBHistoric
WHERE
    "Id" = 12345 AND EXTRACT (DAY FROM "RecordTime") = 1

这提供了类似的数据

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

，但我无法获得每组的第一个值。

原文

I have a database containing time stamped data values, with multiple samples per day, 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倚栏听风 2024-11-12 09:11:34

这是 SQL Server 的解决方案。这个想法是使用相关子查询来识别
每年和每月的最短RecordTime。将这个想法转化为您喜欢的 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))

编辑：如果您的数据集很大，那么加入子查询应该会快得多，但可能更难转化为您的 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

编辑 2：以下代码是我上面第一个查询的标准 SQL-92 版本（在 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))

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))

回复收藏 0 原文

~没有更多了~