如何基于表名与日期列清单实现各表日期维度行统计的DRY方案？

问题背景 #

需要对一组数据表按日期维度统计每张表的总行数，以及X列的非空行数，但每张表的日期列名称各不相同。

常规的重复式实现（不符合DRY原则）：

SELECT 'TableA' AS 'TableName', [AsOfDate], COUNT(*) AS 'Rowcount', SUM(IIF([X] IS NULL,0,1)) AS 'NonEmpty'
FROM TableA GROUP BY [AsOfDate]
UNION ALL
SELECT 'TableB' AS 'TableName', [Snapshot Date], COUNT(*) AS 'Rowcount', SUM(IIF([X] IS NULL,0,1)) AS 'NonEmpty'
FROM TableB GROUP BY [Snapshot Date]
-- ... 重复添加UNION ALL处理TableC、D、E等表

期望基于包含表名与对应日期列的清单表实现，避免重复代码，示例思路如下：

WITH Tables AS ( 
    SELECT * FROM ( VALUES
        ('TableA', 'AsOfDate'),
        ('TableB', 'Snapshot Date'),
        -- ...
        ('TableZ', 'Date of Record')
    ) AS Tables([Table],[DateColumn]) 
)
SELECT MyFn([Table],[DateColumn]) FROM Tables

预期输出格式：

[Table]    [Date]    [Rows]    [NonEmpty]
TableA     2022-01-01    20    18
TableA     2022-01-02    20    19
TableA     2022-01-03    20     0
TableB     2022-01-01    30    28
-- ...

原本计划用接收表名和列名的函数执行动态SQL，但该方法不可行，现提供符合DRY原则的解决方案。

解决方案 #

方法1：基于清单表生成动态SQL #

利用清单表自动拼接所有表的统计语句，一次性执行，无需手动重复编写：

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = @SQL + 
    'SELECT ''' + [Table] + ''' AS [TableName], [' + [DateColumn] + '] AS [Date], COUNT(*) AS [Rows], SUM(IIF([X] IS NULL, 0, 1)) AS [NonEmpty]
     FROM [' + [Table] + '] GROUP BY [' + [DateColumn] + ']
     UNION ALL '
FROM (
    VALUES
        ('TableA', 'AsOfDate'),
        ('TableB', 'Snapshot Date'),
        ('TableZ', 'Date of Record')
) AS Tables([Table],[DateColumn])

-- 移除末尾多余的UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10)

-- 执行生成的统计语句
EXEC sp_executesql @SQL

方法2：结合系统视图自动化生成（适用于SQL Server） #

如果目标表集中在特定Schema下，可通过系统视图自动匹配表和日期列，进一步减少维护成本：

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = @SQL + 
    'SELECT ''' + t.name + ''' AS [TableName], [' + c.name + '] AS [Date], COUNT(*) AS [Rows], SUM(IIF([X] IS NULL, 0, 1)) AS [NonEmpty]
     FROM dbo.[' + t.name + '] GROUP BY [' + c.name + ']
     UNION ALL '
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.schema_id = SCHEMA_ID('dbo')
    AND t.name IN ('TableA', 'TableB', 'TableZ') -- 指定目标表
    AND c.name IN ('AsOfDate', 'Snapshot Date', 'Date of Record') -- 对应日期列

SET @SQL = LEFT(@SQL, LEN(@SQL) - 10)
EXEC sp_executesql @SQL

说明 #

• 两种方法均符合DRY原则，后续新增表只需修改清单表或系统视图的筛选条件即可，无需重复编写统计逻辑。
• 若表名或日期列包含特殊字符，需额外处理转义，确保SQL语句的合法性。

内容的提问来源于stack exchange，提问作者alazyworkaholic