PowerShell+SQL脚本改造：获取近三周同工作日小时销售平均值

原有脚本背景 #

我有一个PowerShell脚本，每日清晨运行，用于收集昨日的每小时销售总额并提交给管理人员，核心代码如下：

$yesterday = 1
[datetime]$startDate = (Get-Date).AddDays(-$yesterday).ToString('yyyy-MM-dd')
[datetime]$endDate = (Get-Date).AddDays(-$yesterday).ToString('yyyy-MM-dd')
$excelStartDate = $startDate.GetDateTimeFormats()[0]
$excelEndDate = $endDate.GetDateTimeFormats()[0]
$fileDateYesterday = (Get-Date).AddDays(-$yesterday)

$sql1 = "
USE STORESQL
    SELECT FORMAT(TRS.F254, 'MM-d-yyyy') AS 'Date',
           'Time' = CASE 
                WHEN TRS.F01 = 06 THEN '6 AM'
                 WHEN TRS.F01 = 07 THEN '7 AM'
                 WHEN TRS.F01 = 08 THEN '8 AM'
                 WHEN TRS.F01 = 09 THEN '9 AM'
                 WHEN TRS.F01 = 10 THEN '10 AM'
                 WHEN TRS.F01 = 11 THEN '11 AM'
                 WHEN TRS.F01 = 12 THEN '12 PM'
                 WHEN TRS.F01 = 13 THEN '1 PM'
                 WHEN TRS.F01 = 14 THEN '2 PM'
                 WHEN TRS.F01 = 15 THEN '3 PM'
                 WHEN TRS.F01 = 16 THEN '4 PM'
                 WHEN TRS.F01 = 17 THEN '5 PM'
                 WHEN TRS.F01 = 18 THEN '6 PM'
                 WHEN TRS.F01 = 19 THEN '7 PM'
                 WHEN TRS.F01 = 20 THEN '8 PM'
                 WHEN TRS.F01 = 21 THEN '9 PM'
                 WHEN TRS.F01 = 22 THEN '10 PM'
                 WHEN TRS.F01 = 23 THEN '11 PM'
           END,
           SUM(TRS.F65) AS 'Total Sales',      
           SUM(TRS.F64) AS 'Transactions',
           (round(SUM(TRS.F65)/SUM(TRS.F64), 2)) AS 'Avg Basket Size'
    FROM RPT_HOU AS TRS 
            JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
            JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 and TRS.F1057=LNK.F1057
    WHERE TRS.F1031= 'D' AND 
            TRS.F254 BETWEEN '$startDate' and '$endDate' AND
            TRS.F1034 BETWEEN '2' and '2' AND
            TRS.F01 BETWEEN '06' and '22' AND
            LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')
    GROUP BY TRS.F254, TRS.F01
    ORDER BY TRS.F254
"

该脚本输出按小时统计的日期、时段、总销售额、交易笔数、客单价数据。

当前需求与问题 #

现在需要获取昨日及过去三周内与昨日同工作日的每小时销售平均值（例如昨日是周一，则取过去三个周一的数据）。我尝试在SQL查询中添加AVG函数并硬编码日期测试，但未成功实现需求，想问是否需要完全重写查询，寻求技术帮助。

解决方案：无需完全重写，调整筛选与聚合逻辑即可 #

1. 先调整PowerShell的日期计算逻辑 #

先自动计算出需要统计的4个目标日期（昨日、上周同日、上上周同日、上上上周同日），避免硬编码：

$yesterday = (Get-Date).AddDays(-1).Date
# 生成过去三周的同工作日日期列表
$dateList = @(
    $yesterday,
    $yesterday.AddDays(-7),
    $yesterday.AddDays(-14),
    $yesterday.AddDays(-21)
)
# 转换为SQL可识别的格式，用单引号包裹并逗号分隔
$dateFilter = $dateList | ForEach-Object { "'$($_.ToString('yyyy-MM-dd'))'" } -join ','

$excelStartDate = $yesterday.GetDateTimeFormats()[0]
$excelEndDate = $yesterday.GetDateTimeFormats()[0]
$fileDateYesterday = $yesterday

2. 修改SQL查询：两种实现方式 #

方式一：同时展示每日数据与平均值

如果需要单独查看每一个目标日期的小时数据，再自行计算平均值，可使用以下查询：

USE STORESQL
SELECT 
    CASE 
        WHEN TRS.F254 = CAST('$yesterday' AS DATE) THEN '昨日'
        ELSE '历史同工作日'
    END AS '数据类型',
    FORMAT(TRS.F254, 'MM-d-yyyy') AS 'Date',
    'Time' = CASE 
        WHEN TRS.F01 = 06 THEN '6 AM'
        WHEN TRS.F01 = 07 THEN '7 AM'
        WHEN TRS.F01 = 08 THEN '8 AM'
        WHEN TRS.F01 = 09 THEN '9 AM'
        WHEN TRS.F01 = 10 THEN '10 AM'
        WHEN TRS.F01 = 11 THEN '11 AM'
        WHEN TRS.F01 = 12 THEN '12 PM'
        WHEN TRS.F01 = 13 THEN '1 PM'
        WHEN TRS.F01 = 14 THEN '2 PM'
        WHEN TRS.F01 = 15 THEN '3 PM'
        WHEN TRS.F01 = 16 THEN '4 PM'
        WHEN TRS.F01 = 17 THEN '5 PM'
        WHEN TRS.F01 = 18 THEN '6 PM'
        WHEN TRS.F01 = 19 THEN '7 PM'
        WHEN TRS.F01 = 20 THEN '8 PM'
        WHEN TRS.F01 = 21 THEN '9 PM'
        WHEN TRS.F01 = 22 THEN '10 PM'
        WHEN TRS.F01 = 23 THEN '11 PM'
    END,
    SUM(TRS.F65) AS 'Total Sales',      
    SUM(TRS.F64) AS 'Transactions',
    ROUND(SUM(TRS.F65)/NULLIF(SUM(TRS.F64), 0), 2) AS 'Avg Basket Size'
FROM RPT_HOU AS TRS 
JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 AND TRS.F1057=LNK.F1057
WHERE TRS.F1031= 'D' 
    AND TRS.F254 IN ($dateFilter)
    AND TRS.F1034 BETWEEN '2' AND '2' 
    AND TRS.F01 BETWEEN '06' AND '22' 
    AND LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')
GROUP BY TRS.F254, TRS.F01
ORDER BY TRS.F254, TRS.F01

方式二：直接输出昨日数据与三周平均值对比

如果需要直接得到每小时的三周平均值，并与昨日数据对比，可使用嵌套查询：

USE STORESQL
SELECT 
    'Time' = CASE 
        WHEN F01 = 06 THEN '6 AM'
        WHEN F01 = 07 THEN '7 AM'
        WHEN F01 = 08 THEN '8 AM'
        WHEN F01 = 09 THEN '9 AM'
        WHEN F01 = 10 THEN '10 AM'
        WHEN F01 = 11 THEN '11 AM'
        WHEN F01 = 12 THEN '12 PM'
        WHEN F01 = 13 THEN '1 PM'
        WHEN F01 = 14 THEN '2 PM'
        WHEN F01 = 15 THEN '3 PM'
        WHEN F01 = 16 THEN '4 PM'
        WHEN F01 = 17 THEN '5 PM'
        WHEN F01 = 18 THEN '6 PM'
        WHEN F01 = 19 THEN '7 PM'
        WHEN F01 = 20 THEN '8 PM'
        WHEN F01 = 21 THEN '9 PM'
        WHEN F01 = 22 THEN '10 PM'
        WHEN F01 = 23 THEN '11 PM'
    END,
    -- 昨日单独数据
    MAX(CASE WHEN F254 = CAST('$yesterday' AS DATE) THEN SUM_65 END) AS '昨日总销售额',
    MAX(CASE WHEN F254 = CAST('$yesterday' AS DATE) THEN SUM_64 END) AS '昨日交易笔数',
    MAX(CASE WHEN F254 = CAST('$yesterday' AS DATE) THEN ROUND(SUM_65/NULLIF(SUM_64,0),2) END) AS '昨日客单价',
    -- 过去三周同工作日的平均值
    AVG(SUM_65) AS '三周平均销售额',
    AVG(SUM_64) AS '三周平均交易笔数',
    ROUND(AVG(SUM_65/NULLIF(SUM_64,0)),2) AS '三周平均客单价'
FROM (
    -- 子查询：先按日期和小时聚合每日基础数据
    SELECT 
        TRS.F254,
        TRS.F01,
        SUM(TRS.F65) AS SUM_65,
        SUM(TRS.F64) AS SUM_64
    FROM RPT_HOU AS TRS 
    JOIN TLZ_TAB TLZ ON TRS.F1034=TLZ.F1034
    JOIN LNK_TAB LNK ON TRS.F1056=LNK.F1056 AND TRS.F1057=LNK.F1057
    WHERE TRS.F1031= 'D' 
        AND TRS.F254 IN ($dateFilter)
        AND TRS.F1034 BETWEEN '2' AND '2' 
        AND TRS.F01 BETWEEN '06' AND '22' 
        AND LNK.F1000 NOT IN ('DSM', 'PAL', 'RAL', 'XAL')
    GROUP BY TRS.F254, TRS.F01
) AS DailyHourlyData
GROUP BY F01
ORDER BY F01

关键说明 #

• 无需完全重写原有查询，仅需调整日期筛选条件（用IN精准匹配目标日期）和聚合层级
• 添加NULLIF函数避免交易笔数为0时的除数报错
• 第二种方式直接输出对比数据，更贴合管理人员的查看需求

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