SQLite表格转置需求：将按周datetime数据转为7列布局

云上老周

2026-6-30

SQLite 数据转置解决方案

要实现将按天存储的数据转置为7列的周格式，核心思路是先给每条数据分配所属的周组，再通过条件聚合将同组的七天数据横向排列。假设你的表名为daily_readings，可以用以下SQL语句实现：

WITH ranked_data AS (
    -- 标准化星期几的大小写，并给每条数据分配组号（每7条为一组，对应一周）
    SELECT
        LOWER(Weekday) AS weekday,
        reading_datetime,
        -- 按时间排序后，每7条划分为一组
        (ROW_NUMBER() OVER (ORDER BY reading_datetime) - 1) / 7 AS week_group
    FROM daily_readings
)
-- 按组聚合，将不同星期的数据转成列
SELECT
    MAX(CASE WHEN weekday = 'sunday' THEN reading_datetime END) AS sunday,
    MAX(CASE WHEN weekday = 'monday' THEN reading_datetime END) AS monday,
    MAX(CASE WHEN weekday = 'tuesday' THEN reading_datetime END) AS tuesday,
    MAX(CASE WHEN weekday = 'wednesday' THEN reading_datetime END) AS wednesday,
    MAX(CASE WHEN weekday = 'thursday' THEN reading_datetime END) AS thursday,
    MAX(CASE WHEN weekday = 'friday' THEN reading_datetime END) AS friday,
    MAX(CASE WHEN weekday = 'saturday' THEN reading_datetime END) AS saturday
FROM ranked_data
GROUP BY week_group
ORDER BY week_group;

关键说明：

标准化星期格式：用LOWER(Weekday)统一大小写，避免因sunday和Sunday的差异导致匹配失败。
分组逻辑：(ROW_NUMBER() OVER (ORDER BY reading_datetime) - 1) / 7会把按时间排序后的每7条数据分到同一个week_group里，确保每组对应完整的一周。
条件聚合：通过CASE语句筛选对应星期的数据，再用MAX（或MIN，这里因为每组每个星期只有一条数据，所以两者效果一样）将值提取为列。