英文:
SQL query to pull a single row for each date and the associated max date_time stamp
问题
我有一个包含日期列的表，显示了给定日期的温度数据，以及另一列显示生成这些数据的日期和时间，如下所示：
我想要一个查询，该查询可以提取与最近的DATA_UPDATE_DATETIME数据相关的每个DATE_STAMP数据的单行数据。
结果应该如下所示：
我尝试了几个查询，尝试使用分区或内连接，但未能解决问题。例如：
select distinct
DATE_STAMP,
min(DATA_UPDATE_DATETIME) over (partition by DATE_STAMP),
MIN_TEMP,
MAX_TEMP
from
table
请注意，这只是代码部分的翻译，不包括问题的回答。如果您需要进一步的帮助，请随时提出。
英文:
I have a table with a date column which displays temperature data for a given date as well as another column which indicates the date and time that this data is generated as follows:
I would like a query which pulls a single row of data for each DATE_STAMP data that correlates to the most recent DATA_UPDATE_DATETIME data.
The result for should be as follows:
I've tried several queries which attempts to utilize partitioning or inner joining but have not managed to solve the problem. For example:
select distinct
DATE_STAMP,
min(DATA_UPDATE_DATETIME) over (partition by DATE_STAMP),
MIN_TEMP,
MAX_TEMP,
from
table
答案1
得分: 0
你可以通过首先构建一个包含不同的DATE_STAMP值和相应最新UPDATE值的表，然后在完整表上连接到该表来获得所需的结果。
例如
使用 x 作为
(
选择 DATE_STAMP, MAX(DATA_UPDATE_DATETIME) 作为 [MaxUpdate]
从 theTable
分组按 DATE_STAMP
)
选择
x.DATE_STAMP,
x.MaxUpdate,
t.MIN_TEMP,
t.MAX_TEMP
从 x
内连接 theTable t
在 t.DATE_STAMP = x.DATE_STAMP
和 t.DATA_UPDATE_DATETIME = x.MaxUpdate
你可能需要考虑的一件事是，如果最终有两行具有相同的DATE_STAMP和DATA_UPDATE_DATETIME值，但不同的MIN_TEMP和/或MAX_TEMP值（这可能是可能的，除非你在这些列上有一些唯一约束，并且根据需要管理数据插入/更新）。否则，你可能会出现对于特定的DATE_STAMP / DATA_UPDATE_DATETIME组合有多行的机会。
英文:
You can achieve this by first building a table of the distinct DATE_STAMP values with the corresponding latest UPDATE value, and then join to that on the full table to get the desired result.
For example
WITH x AS
(
SELECT DATE_STAMP, MAX(DATA_UPDATE_DATETIME) AS [MaxUpdate]
FROM theTable
GROUP BY DATE_STAMP
)
SELECT
x.DATE_STAMP,
x.MaxUpdate,
t.MIN_TEMP,
t.MAX_TEMP
FROM x
INNER JOIN theTable t
ON t.DATE_STAMP = x.DATE_STAMP
AND t.DATA_UPDATE_DATETIME = x.MaxUpdate
One thing you may want to consider is, what is your expectation if there happens to end up being two rows with the same DATE_STAMP and DATA_UPDATE_DATETIME values, but different MIN_TEMP and/or MAX_TEMP values (which could be possible, I guess, unless you've got some unique constraint on those columns, and are managing the data inserts/updates accordingly). Otherwise, you've got a chance that you'll end up with more than one row for a particular DATE_STAMP / DATA_UPDATE_DATETIME combination
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论