按月度格式汇总每日未使用设备报表的技术需求

核心思路 #

不用每日编写子查询的关键是生成当月所有日期的数据集，将每个活跃设备与这些日期做全量配对，再逐一验证该设备在当天的目标班次是否无使用记录，最终输出每日未使用设备清单或汇总统计。

优化后的SQL查询 #

WITH month_dates AS (
    -- 针对各分支时区生成当月所有本地日期
    SELECT 
        CASE
            WHEN b."Id" = 'b2d5e35e-ce26-66d8-cb9f-42d1c92ad3f6' THEN date_trunc('day', generate_series(
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AEST'),
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AEST') + INTERVAL '1 month - 1 day',
                INTERVAL '1 day'
            ) AT TIME ZONE 'AEST')::DATE
            WHEN b."Id" IN ('c9d2a7f8-59c8-5326-ae0f-706c032501c7', '4e393240-ca96-3480-62b3-42cdc1a7ee22') THEN date_trunc('day', generate_series(
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AEDT'),
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AEDT') + INTERVAL '1 month - 1 day',
                INTERVAL '1 day'
            ) AT TIME ZONE 'AEDT')::DATE
            WHEN b."Id" = 'eedefb97-c674-4a48-87a3-acf70027cd61' THEN date_trunc('day', generate_series(
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AWST'),
                date_trunc('month', CURRENT_DATE AT TIME ZONE 'AWST') + INTERVAL '1 month - 1 day',
                INTERVAL '1 day'
            ) AT TIME ZONE 'AWST')::DATE
        END AS local_date,
        b."Id" AS branch_id
    FROM "Branch" b
    WHERE b."Id" IN ('b2d5e35e-ce26-66d8-cb9f-42d1c92ad3f6', 'c9d2a7f8-59c8-5326-ae0f-706c032501c7', '4e393240-ca96-3480-62b3-42cdc1a7ee22', 'eedefb97-c674-4a48-87a3-acf70027cd61')
),
device_daily AS (
    -- 关联设备与当月每日日期，生成设备-日期全量组合
    SELECT
        t."Id" AS "TruckId",
        t."REGO",
        t."FleetNumber" AS "Fleet Number",
        b."Name" AS "Branch",
        md.local_date AS "Date"
    FROM "Truck" t
    JOIN "Branch" b ON b."Id" = t."BranchId"
    JOIN month_dates md ON md.branch_id = b."Id"
    WHERE t."SubcontractorId" IS NULL 
      AND t."IsActive" IS TRUE
)
-- 筛选每日未在目标班次（4:00-15:00）使用的设备
SELECT
    dd."REGO",
    dd."Fleet Number",
    dd."Branch",
    dd."Date"
FROM device_daily dd
JOIN "Branch" b ON b."Name" = dd."Branch"
WHERE NOT EXISTS (
    SELECT 1
    FROM "JobLeg" jl
    WHERE jl."TruckId" = dd."TruckId"
      AND (
         (b."Id" = 'b2d5e35e-ce26-66d8-cb9f-42d1c92ad3f6' and 
          (jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEST')::DATE = dd."Date"
          AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEST') >= 4 
          AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEST') < 15)
        OR
        ((b."Id" = 'c9d2a7f8-59c8-5326-ae0f-706c032501c7' or b."Id" = '4e393240-ca96-3480-62b3-42cdc1a7ee22') 
         AND (jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEDT')::DATE = dd."Date"
         AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEDT') >= 4 
         AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AEDT') < 15)
        OR
        (b."Id" = 'eedefb97-c674-4a48-87a3-acf70027cd61' and
         (jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AWST')::DATE = dd."Date"
         AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AWST') >= 4 
         AND extract(HOUR from jl."EndDate" AT TIME ZONE 'UTC' AT TIME ZONE 'AWST') < 15)
      )
)
ORDER BY dd."Date", dd."Branch", dd."REGO";

关键说明 #

1. month_dates CTE：针对每个分支的时区生成当月所有本地日期，确保日期判断与分支时区完全一致。如果需要指定固定月份（而非当前月），将CURRENT_DATE替换为目标月份的任意日期即可，比如'2024-09-01'::DATE。
2. device_daily CTE：将每个符合条件的活跃设备与当月每一天配对，得到设备-日期的全量组合，避免了每日编写子查询的重复工作。
3. NOT EXISTS 子句：替换原查询的整月判断逻辑，改为验证设备在当天的目标班次是否无作业记录。
4. 若需要汇总统计（比如每个设备当月未使用天数），可将最终查询替换为：

SELECT
    dd."REGO",
    dd."Fleet Number",
    dd."Branch",
    COUNT(dd."Date") AS "Unused Days in Month"
FROM device_daily dd
JOIN "Branch" b ON b."Name" = dd."Branch"
WHERE NOT EXISTS (
    -- 保留上述NOT EXISTS内的判断逻辑
)
GROUP BY dd."REGO", dd."Fleet Number", dd."Branch"
ORDER BY dd."Branch", dd."REGO";

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