SQL Server - 使用聚集索引时如何存储数据页

Question

SqlSandwiches

Asked: 2013-06-19 10:55:42 +0800 CST2013-06-19 10:55:42 +0800 CST 2013-06-19 10:55:42 +0800 CST

试图找出值最后一次改变的时间

772

我有一个包含 ID、值和日期的表。此表中有许多 ID、值和日期。

记录会定期插入到该表中。ID 将始终保持不变，但偶尔值会更改。

我如何编写一个查询，它会给我 ID 加上值更改的最近时间？注意：该值将始终增加。

从这个样本数据：

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

结果应该是：

Taco_ID      Taco_date
1            2012-07-01 00:00:05

（因为 00:05 是最后一次Taco_Value更改。）

8 个回答

Voted

Aaron Bertrand · Answer 1 · 2013-06-19T11:09:58+08:00

Taco_value这两个查询依赖于始终随时间增加的假设。

;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y 
WHERE rn = 1;

具有较少窗口函数疯狂的替代方案：

;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date, 
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

SQLfiddle上的示例

更新

对于那些跟踪的人来说，如果Taco_value可以重复会发生什么，则存在争议。如果它可以从 1 变为 2，然后对于任何给定的 1 Taco_ID，则查询将不起作用。这是针对这种情况的解决方案，即使它不是像 Itzik Ben-Gan 这样的人可能能够梦想的间隙和岛屿技术，即使它与 OP 的场景无关 - 它可能是与未来的读者相关。它有点复杂，我还添加了一个额外的变量 - aTaco_ID只有一个Taco_value.

如果要包含任何 ID 的第一行，其中值在整个集合中根本没有改变：

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT  
  main.Taco_ID, 
  Taco_date = MIN(CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
) 
GROUP BY main.Taco_ID;

如果你想排除这些行，它会更复杂一些，但仍然是微小的变化：

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT 
  main.Taco_ID, 
  Taco_date = MIN(
  CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause ***** 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

更新了 SQLfiddle 示例

Andriy M · Answer 2 · 2013-06-20T01:04:49+08:00

Andriy M

2013-06-20T01:04:49+08:002013-06-20T01:04:49+08:00

基本上，这是@Taryn 的建议“浓缩”为没有派生表的单个 SELECT：

SELECT DISTINCT
  Taco_ID,
  Taco_date = MAX(MIN(Taco_date)) OVER (PARTITION BY Taco_ID)
FROM Taco
GROUP BY
  Taco_ID,
  Taco_value
;

注意：此解决方案考虑了Taco_value只能增加的规定。（更准确地说，它假设Taco_value不能变回以前的值——实际上与链接的答案相同。）

查询的 SQL Fiddle 演示：http ://sqlfiddle.com/#!3/91368/2

13

Taryn · Answer 3 · 2013-06-19T11:15:32+08:00

Taryn

2013-06-19T11:15:32+08:002013-06-19T11:15:32+08:00

您应该能够同时使用min()和max()聚合函数得到结果：

select t1.Taco_ID, MAX(t1.taco_date) Taco_Date
from taco t1
inner join
(
    select MIN(taco_date) taco_date,
        Taco_ID, Taco_value
    from Taco
    group by Taco_ID, Taco_value
) t2
    on t1.Taco_ID = t2.Taco_ID
    and t1.Taco_date = t2.taco_date
group by t1.Taco_Id

请参阅带有演示的 SQL Fiddle

7

ypercubeᵀᴹ · Answer 4 · 2013-06-19T12:29:46+08:00

另一个答案是基于这些值不会重新出现的假设（这基本上是@Aaron 的查询 2，浓缩在一个更少的嵌套中）：

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MIN(Taco_date) DESC),
    Taco_date = MIN(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT Taco_ID, Taco_value, Taco_date
FROM x 
WHERE Rn = 1 ;

测试：SQL-Fiddle

以及一个更普遍的问题的答案，其中值可以重新出现：

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.Taco_ID, Taco_date = MIN(t.Taco_date)
FROM x
  JOIN dbo.Taco t
    ON  t.Taco_ID = x.Taco_ID
    AND t.Taco_date > x.Taco_date
WHERE x.Rn = 2 
GROUP BY t.Taco_ID ;

（或使用CROSS APPLY所有相关行，包括value，显示）：

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.*
FROM x
  CROSS APPLY 
  ( SELECT TOP (1) *
    FROM dbo.Taco t
    WHERE t.Taco_ID = x.Taco_ID
      AND t.Taco_date > x.Taco_date
    ORDER BY t.Taco_date
  ) t
WHERE x.Rn = 2 ;

测试：SQL-Fiddle-2

Kenneth Fisher · Answer 5 · 2013-06-19T11:17:04+08:00

Kenneth Fisher

2013-06-19T11:17:04+08:002013-06-19T11:17:04+08:00

仅供参考 +1 用于提供样本结构和数据。我唯一可以要求的是该数据的预期输出。

编辑：这个会让我发疯。我只是新手，有一种“简单”的方法可以做到这一点。我摆脱了不正确的解决方案，并提出了一个我认为是正确的解决方案。这是一个类似于@bluefeets 的解决方案，但它涵盖了@AaronBertrand 提供的测试。

;WITH TacoMin AS (SELECT Taco_ID, Taco_value, MIN(Taco_date) InitialValueDate
                FROM Taco
                GROUP BY Taco_ID, Taco_value)
SELECT Taco_ID, MAX(InitialValueDate)
FROM TacoMin
GROUP BY Taco_ID

2

JJ_Coder4Hire · Answer 6 · 2018-09-21T21:39:22+08:00

JJ_Coder4Hire

2018-09-21T21:39:22+08:002018-09-21T21:39:22+08:00

为什么不直接获取滞后值和超前值的差值？如果差异为零，它没有改变，它不是零，然后它改变了。这可以通过一个简单的查询来完成：

-- example gives the times the value changed in the last 24 hrs
SELECT
    LastUpdated, [DiffValue]
FROM (
  SELECT
      LastUpdated,
      a.AboveBurdenProbe1TempC - coalesce(lag(a.AboveBurdenProbe1TempC) over (order by ProcessHistoryId), 0) as [DiffValue]
  FROM BFProcessHistory a
  WHERE LastUpdated > getdate() - 1
) b
WHERE [DiffValue] <> 0
ORDER BY LastUpdated ASC

1

user2846273 · Answer 7 · 2020-02-29T15:14:31+08:00

今天有一个类似的问题 - 在 Power BI 中，我可以使用 Tabler.FillDown 解决它。经过一番搜索，我找到了 FillDown 的 SQL 变体：

https://www.oraylis.de/blog/fill-down-table-in-t-sql-last-non-empty-value

因此，我花时间将该解决方案调整到此示例 - 添加了一个额外的行以显示 Taco_value 的重用。

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:09')

注意：此解决方案考虑到 Taco_value 可以增加和减少（或变回以前的值）

WITH help1
 AS (SELECT 
         *
        ,[ChangeIndicator] = CASE
                                 WHEN [Taco_value] = LAG([Taco_value],1) OVER(
                                      ORDER BY 
         [Taco_ID]) THEN 0
                                 ELSE 1
                             END
        ,[Taco_lag] = LAG([Taco_value],1) OVER(
         ORDER BY 
         [Taco_date])
     FROM [Taco]),
 help2
 AS (SELECT 
         *
        ,[RowGroup] = SUM([ChangeIndicator]) OVER(
         ORDER BY 
         [Taco_date])
     FROM [help1])
 SELECT 
     *
    ,[ChangeDate] = FIRST_VALUE([Taco_date]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
    ,[Taco_FillDown] = FIRST_VALUE([Taco_lag]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
 FROM [help2]
 ORDER BY 
     [Taco_date]

结果：

pmc086 · Answer 8 · 2013-06-19T22:36:20+08:00

pmc086

2013-06-19T22:36:20+08:002013-06-19T22:36:20+08:00

这可以像下面这样简单吗？

       SELECT taco_id, MAX(
             CASE 
                 WHEN taco_value <> MAX(taco_value) 
                 THEN taco_date 
                 ELSE null 
             END) AS last_change_date

鉴于 taco_value 总是增加？

ps我自己是SQL初学者，但是学习缓慢但肯定。

-1

试图找出值最后一次改变的时间

更新

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

授予用户对所有表的访问权限

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

试图找出值最后一次改变的时间

8 个回答

更新

相关问题