SQL Server 按月运行总计

vawmfj5a 于 2023-01-20 发布在其他

关注(0)|答案(1)|浏览(120)

我有一个表，其中包含每天收集的每个表的行数。我尝试进行查询，并最终将其放入报表中，这将为我提供6、5、4、3、2和1个月前每个表的总行数。有了这些数字后，我可以计算百分比变化或绘制图表。目前，这种方法可以正常工作，但在我添加更多的月份后就停止工作了。而且它只是大量的重复代码。想知道是否有人想出了更有效的方法来做到这一点。

DECLARE @DT1 DATETIME,
        @DT2 DATETIME,
        @DT3 DATETIME,
        @DT4 DATETIME;

SELECT @DT1 = MAX(AnalysisDate)
FROM DBA_TableSpaceUsedHistory;
SELECT @DT2 = MAX(AnalysisDate)
FROM DBA_TableSpaceUsedHistory
WHERE AnalysisDate < DATEADD(hh, -23, @DT1);
SELECT @DT3 = MAX(AnalysisDate)
FROM DBA_TableSpaceUsedHistory
WHERE AnalysisDate < DATEADD(mm, -1, DATEADD(hh, 1, @DT1));
SELECT @DT4 = MAX(AnalysisDate)
FROM DBA_TableSpaceUsedHistory
WHERE AnalysisDate < DATEADD(mm, -3, DATEADD(hh, 1, @DT1));

-- SELECT @DT1, @DT2, @DT3

SELECT TOP 5
       t1.TableSchema,
       t1.TableName,
       FORMAT(t3.TableRows, '#,##0') [3 Months Value],
       FORMAT(t2.TableRows, '#,##0') [Day Old Value],
       FORMAT(t1.TableRows, '#,##0') [New Value],
       FORMAT((t1.TableRows - t2.TableRows), '#,##0') [1 Day Difference],
       CASE WHEN t2.TotalSpaceKB > 0 THEN FORMAT((((t1.TableRows - t2.TableRows) / (t2.TableRows * 1.0)) * 100.0), '##0.00')
            ELSE '100'
       END AS '% Difference',
       FORMAT((t1.TableRows - t3.TableRows), '#,##0') [3 Month Difference],
       CASE WHEN t2.TotalSpaceKB > 0 THEN FORMAT((((t1.TableRows - t3.TableRows) / (t3.TableRows * 1.0)) * 100.0), '##0.00')
            ELSE '100'
       END AS '% Difference'
FROM DBA_TableSpaceUsedHistory t1
    INNER JOIN DBA_TableSpaceUsedHistory t2
        ON t1.TableSchema = t2.TableSchema
           AND t1.TableName = t2.TableName
    INNER JOIN DBA_TableSpaceUsedHistory t3
        ON t1.TableSchema = t3.TableSchema
           AND t1.TableName = t3.TableName
WHERE t1.AnalysisDate = @DT1
      AND t2.AnalysisDate = @DT2
      AND t1.AnalysisDate = @DT1
      AND t3.AnalysisDate = @DT4
ORDER BY t1.TableRows - t2.TableRows DESC;

SELECT *
FROM DBA_TableSpaceUsedHistory;

结果是这样的，这是一种想法，但我想回去几个月。感谢任何提示！！

sql-server

来源：https://stackoverflow.com/questions/75097017/running-totals-by-month

1条答案

按热度按时间

0x6upsns1#

下面是一个使用LAG和ROW_NUMBER窗口函数的简单示例，应该可以帮助您入门。

SELECT TableSchema, TableName, TableRows AS NewValue,
LastMonthValue, TwoMonthsValue, ThreeMonthsValue
FROM (
  SELECT *,
    -- window functions to select previous rows. I've included three,
    -- just add however many needed depending on how far you want to go back
    LAG(TableRows,1) OVER (PARTITION BY TableSchema, TableName ORDER BY AnalysisDate) AS LastMonthValue,
    LAG(TableRows,2) OVER (PARTITION BY TableSchema, TableName ORDER BY AnalysisDate) AS TwoMonthsValue,
    LAG(TableRows,3) OVER (PARTITION BY TableSchema, TableName ORDER BY AnalysisDate) AS ThreeMonthsValue,
    -- The RowNum (below) together with the final WHERE clause
    -- means you always get the row with the latest AnalysisDate per partition,
    -- The LAGs allow you to see the older rows as far back as desired
    ROW_NUMBER() OVER (PARTITION BY TableSchema, TableName ORDER BY AnalysisDate DESC) AS RowNum
  FROM (
    -- sample data (filtered or grouped as monthly)
    -- you could perhaps do some other join if you need a total for yesterday included
    SELECT 'dbo', 'Session', 10, '2022-01-01' UNION ALL
    SELECT 'dbo', 'Session', 20, '2022-02-01' UNION ALL
    SELECT 'dbo', 'Session', 30, '2022-03-01' UNION ALL
    SELECT 'dbo', 'Session', 40, '2022-04-01' UNION ALL
    SELECT 'dbo', 'UploadRates', 100, '2022-01-01' UNION ALL
    SELECT 'dbo', 'UploadRates', 200, '2022-02-01' UNION ALL
    SELECT 'dbo', 'UploadRates', 300, '2022-03-01' UNION ALL
    SELECT 'dbo', 'UploadRates', 400, '2022-04-01'
  ) AS DBA_TableSpaceUsedHistory (TableSchema, TableName, TableRows, AnalysisDate)
) AS t
WHERE RowNum = 1;

输出：
| 表架构|表格名称|新值|上月值|两个月价值|三个月价值|
| - ------|- ------|- ------|- ------|- ------|- ------|
| dbo|届会|四十|三十|二十个|十个|
| dbo|上传速率|四百|三百|二百|一百|

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 按月运行总计

1条答案

相关问题

热门标签

最新问答