数仓查询案例

以下是一些基于数仓设计的聚合查询案例，它们涵盖了不同的分析需求。此外，这些查询均使用了星型模型中的事实表和维度表，能够快速获取业务洞察。

1. 查询每个区域的日总有功功率

目标: 统计某一天每个区域（变电站）管理的总有功功率。

SELECT
    dx.Substation AS Substation,         -- 区域名称（变电站）
    dt.Date AS Date,                     -- 日期
    SUM(fpr.ActivePower_Total) AS TotalActivePower -- 总有功功率
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
JOIN
    DimXTU dx ON dftu.XTUKey = dx.XTUKey
WHERE
    dt.Date = '2025-04-20'               -- 指定日期
    AND dftu.IsCurrent = TRUE            -- 确保使用FTU设备的当前有效记录
GROUP BY
    dx.Substation, dt.Date               -- 按区域和日期分组
ORDER BY
    TotalActivePower DESC;               -- 按总有功功率降序排列

2. 查询某时间段内每小时的平均电压

目标: 分析某一时间段内每小时的平均电压（如UAB电压）。

SELECT
    dt.Hour AS Hour,                    -- 小时
    AVG(fpr.UAB_Voltage) AS AvgUABVoltage -- 平均UAB电压
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
WHERE
    dt.Date BETWEEN '2025-04-20' AND '2025-04-21' -- 时间范围
GROUP BY
    dt.Hour                                   -- 按小时分组
ORDER BY
    dt.Hour;                                  -- 按小时排序

3. 查询某设备的某一天的最大电流

目标: 获取某台设备（FTU）的某一天最大电流（例如IA_Current）。

SELECT
    dftu.FTU_ID AS FTU_ID,              -- 设备ID
    dt.Date AS Date,                    -- 日期
    MAX(fpr.IA_Current) AS MaxIACurrent -- 最大IA电流
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
WHERE
    dftu.FTU_ID = 'FTU12345'            -- 指定设备ID
    AND dt.Date = '2025-04-20'          -- 指定日期
GROUP BY
    dftu.FTU_ID, dt.Date;               -- 按设备和日期分组

4. 查询每个设备型号的平均功率

目标: 统计每种FTU设备型号的平均总有功功率。

SELECT
    dftu.FTU_Model AS FTUModel,          -- FTU设备型号
    AVG(fpr.ActivePower_Total) AS AvgActivePower -- 平均总有功功率
FROM
    FactPowerReadings fpr
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
WHERE
    dftu.IsCurrent = TRUE                -- 确保使用FTU设备的当前有效记录
GROUP BY
    dftu.FTU_Model                       -- 按设备型号分组
ORDER BY
    AvgActivePower DESC;                 -- 按平均功率降序排列

5. 查询每日的总功率趋势

目标: 绘制某时间段内每日的总有功功率变化趋势。

SELECT
    dt.Date AS Date,                     -- 日期
    SUM(fpr.ActivePower_Total) AS TotalActivePower -- 每日总有功功率
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
WHERE
    dt.Date BETWEEN '2025-04-01' AND '2025-04-20' -- 时间范围
GROUP BY
    dt.Date                                -- 按日期分组
ORDER BY
    dt.Date;                               -- 按日期排序

6. 查询当前活跃设备的无功功率统计

目标: 获取所有当前活跃设备的无功功率的总和、平均值、最大值和最小值。

SELECT
    COUNT(DISTINCT fpr.FTUKey) AS ActiveDeviceCount, -- 当前活跃设备数量
    SUM(fpr.ReactivePower_Total) AS TotalReactivePower, -- 总无功功率
    AVG(fpr.ReactivePower_Total) AS AvgReactivePower,   -- 平均无功功率
    MAX(fpr.ReactivePower_Total) AS MaxReactivePower,   -- 最大无功功率
    MIN(fpr.ReactivePower_Total) AS MinReactivePower    -- 最小无功功率
FROM
    FactPowerReadings fpr
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
WHERE
    dftu.Status = 'Active';                 -- 仅统计活跃设备

7. 查询每个设备每日的负载时间

目标: 统计某设备每日的负载时间（数据上报记录的数量）。

SELECT
    dftu.FTU_ID AS FTU_ID,               -- 设备ID
    dt.Date AS Date,                     -- 日期
    COUNT(*) AS LoadCount                -- 数据上报记录数
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
WHERE
    dftu.FTU_ID = 'FTU12345'             -- 指定设备ID（可移除以查询所有设备）
    AND dt.Date BETWEEN '2025-04-01' AND '2025-04-07' -- 时间范围
GROUP BY
    dftu.FTU_ID, dt.Date                 -- 按设备和日期分组
ORDER BY
    dt.Date;                             -- 按日期排序

8. 查询某区域的电压偏差情况

目标: 统计某区域（变电站）内的设备电压偏差（例如UAB电压的标准差）。

SELECT
    dx.Substation AS Substation,         -- 区域名称（变电站）
    STDDEV(fpr.UAB_Voltage) AS StdDevUABVoltage -- UAB电压的标准差
FROM
    FactPowerReadings fpr
JOIN
    DimFTU dftu ON fpr.FTUKey = dftu.FTUKey
JOIN
    DimXTU dx ON dftu.XTUKey = dx.XTUKey
WHERE
    dx.Substation = 'Substation_A'       -- 指定区域名称（可移除以查询所有区域）
GROUP BY
    dx.Substation;                       -- 按区域分组

9. 查询每季度的能耗总量

目标: 统计每个季度的总有功功率。

SELECT
    dt.Year AS Year,                     -- 年份
    dt.Quarter AS Quarter,               -- 季度
    SUM(fpr.ActivePower_Total) AS TotalActivePower -- 总有功功率
FROM
    FactPowerReadings fpr
JOIN
    DimTime dt ON fpr.TimestampKey = dt.TimestampKey
GROUP BY
    dt.Year, dt.Quarter                   -- 按年份和季度分组
ORDER BY
    dt.Year, dt.Quarter;                  -- 按年份和季度排序

10. 查询设备离线情况

目标: 统计某一天没有上报数据的设备数量。

SELECT
    COUNT(DISTINCT dftu.FTU_ID) AS OfflineDeviceCount -- 离线设备数量
FROM
    DimFTU dftu
LEFT JOIN
    FactPowerReadings fpr ON dftu.FTUKey = fpr.FTUKey
    AND fpr.ReadingTimestamp BETWEEN '2025-04-20 00:00:00' AND '2025-04-20 23:59:59'
WHERE
    fpr.FTUKey IS NULL                 -- 没有上报数据的设备
    AND dftu.IsCurrent = TRUE;         -- 当前有效设备