SQL创建视图：按日期首尾值计算总发电量累计值差值

嘿，这个需求挺典型的，咱们可以用几种不同的SQL方案来实现，我给你详细拆解下：

核心逻辑是按日期分组，提取每组内total_energy_generated_till的首个值（对应当日最早时间的记录）和末个值（对应当日最晚时间的记录），最后计算两者的差值，再把这个逻辑封装成视图。

方案一：使用窗口函数（适配现代SQL数据库：PostgreSQL、MySQL 8.0+、SQL Server等） #

窗口函数是最简洁的实现方式，通过ROW_NUMBER()给每日的记录按时间排序，标记出第一条和最后一条，再聚合计算差值：

CREATE VIEW daily_energy_diff AS
WITH daily_ranked_records AS (
    SELECT
        -- 把timestamp转成日期类型，若TSTAMP已是DATE类型可直接用TSTAMP
        CAST(TSTAMP AS DATE) AS record_date,
        total_energy_generated_till,
        -- 按日期分组，正序排号，1就是当日第一条记录
        ROW_NUMBER() OVER (PARTITION BY CAST(TSTAMP AS DATE) ORDER BY TSTAMP ASC) AS rn_first,
        -- 按日期分组，逆序排号，1就是当日最后一条记录
        ROW_NUMBER() OVER (PARTITION BY CAST(TSTAMP AS DATE) ORDER BY TSTAMP DESC) AS rn_last
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    record_date,
    MAX(CASE WHEN rn_first = 1 THEN total_energy_generated_till END) AS first_daily_energy,
    MAX(CASE WHEN rn_last = 1 THEN total_energy_generated_till END) AS last_daily_energy,
    -- 计算当日能量差值
    MAX(CASE WHEN rn_last = 1 THEN total_energy_generated_till END) - MAX(CASE WHEN rn_first = 1 THEN total_energy_generated_till END) AS daily_energy_diff
FROM daily_ranked_records
GROUP BY record_date
ORDER BY record_date;

方案二：子查询+聚合（适配老版本SQL，比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以先通过子查询获取每日的最早/最晚时间，再关联原表拿到对应能量值：

CREATE VIEW daily_energy_diff AS
SELECT
    d.record_date,
    first_energy.total_energy_generated_till AS first_daily_energy,
    last_energy.total_energy_generated_till AS last_daily_energy,
    last_energy.total_energy_generated_till - first_energy.total_energy_generated_till AS daily_energy_diff
FROM (
    -- 先分组拿到每日的最早和最晚时间
    SELECT
        CAST(TSTAMP AS DATE) AS record_date,
        MIN(TSTAMP) AS earliest_time,
        MAX(TSTAMP) AS latest_time
    FROM your_table_name -- 替换成你的实际表名
    GROUP BY CAST(TSTAMP AS DATE)
) d
-- 关联原表拿到最早时间对应的能量值
JOIN your_table_name first_energy ON d.earliest_time = first_energy.TSTAMP
-- 关联原表拿到最晚时间对应的能量值
JOIN your_table_name last_energy ON d.latest_time = last_energy.TSTAMP
ORDER BY d.record_date;

注意事项 #

• 记得把代码中的your_table_name替换成你的实际表名
• 如果TSTAMP已经是DATE类型，直接去掉CAST(TSTAMP AS DATE)即可
• 若某天只有一条记录，差值会是0，若需要过滤这种情况，可以在最后加HAVING daily_energy_diff != 0
• 确保total_energy_generated_till是数值类型（INT/FLOAT/DECIMAL等），否则无法进行减法运算

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