主页 / user-94472

choloboy's questions

Martin Hope
choloboy
Asked: 2016-07-23 11:04:29 +0800 CST
  • 0

我下面有以下列。我不太熟悉 OVER 函数。问题是，列usage_change_mb是每日变化（来自每日快照）还是所有正在评估的快照的平均变化？

这是专栏：

( LAST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

- /* subtract */

FIRST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ) * 8 / 1048576.0 
AS usage_change_mb
sql-server t-sql
Martin Hope
choloboy
Asked: 2016-07-19 09:24:31 +0800 CST
  • 0

我有一张超过 6000 万行的表。每天多次为所有实例的所有数据库拍摄快照。我试图通过每天为每个数据库/实例返回 1 个快照来减少选择中的行。

我当前使用的查询（SQL Server 2012）：

SELECT  instance_name ,
        DU.database_name ,
        DU.collection_time ,
        DU.dbsize ,
        DU.logsize ,
        DU.reservedpages ,
        DU.usedpages
FROM    [core].[snapshots_internal] AS [SI]
LEFT JOIN [core].[source_info_internal] AS [SII] ON [SI].[source_id] = [SII].[source_id]
INNER JOIN [core].[snapshot_timetable_internal] AS [STI] ON [SI].[snapshot_time_id] = [STI].[snapshot_time_id]
LEFT JOIN [snapshots].[disk_usage] AS [DU] ON [SI].[snapshot_id] = [DU].[snapshot_id]
WHERE   instance_name LIKE '%\DEV'
        AND DU.[database_name] NOT LIKE 'tempdb'
        AND DU.[database_name] NOT LIKE 'master'
        AND DU.[database_name] NOT LIKE 'model'
        AND DU.[database_name] NOT LIKE 'msdb'
GROUP BY SII.instance_name
ORDER BY SII.instance_name ,
         DU.database_name ,
         DU.collection_time;

返回以下内容：

2015-08-09 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 05:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 11:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 17:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 23:30:04.0000000 +00:00  665600  64960   484170
2015-08-11 05:30:03.0000000 +00:00  665600  64960   484170
2015-08-11 11:30:02.0000000 +00:00  665600  64960   484170
2015-08-11 12:30:28.0000000 +00:00  665600  64960   484170
2015-08-11 17:30:03.0000000 +00:00  665600  64960   484170
2015-08-11 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 05:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 11:30:02.0000000 +00:00  665600  64960   484170

我想获得：

2015-08-09 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-10 23:30:04.0000000 +00:00  665600  64960   484170
2015-08-11 23:30:02.0000000 +00:00  665600  64960   484170
2015-08-12 11:30:02.0000000 +00:00  665600  64960   484170

在对其他堆栈帖子进行一些研究后，我能够找到类似的问题，但回复仍然让我每天返回多行。因此，非常感谢任何建议。

我试过的东西的EX：

select collection_time, du.snapshot_id, du.database_name 
from   snapshots.disk_usage du 
where du.collection_time in 
(
   select max(du.collection_time) 
   from snapshots.disk_usage du 
   group by collection_time
)
sql-server sql-server-2012