在PostgreSQL中如何合理存储员工月度每日工时数据？

你的原始表设计用日期作为列名属于反范式设计，存在诸多问题：每月天数不同需频繁增删列、统计查询SQL复杂度高、难以扩展额外字段。下面是更合理的存储方案，同时完美适配不同月份的天数差异：

1. 范式化单表设计（最推荐） #

用单表存储员工单日工时记录，每行对应一个员工一天的工时，完全无需担心月份天数差异：

CREATE TABLE employee_work_hours (
    employee_id INT REFERENCES employees(id), -- 关联员工主表（若有）
    work_date DATE NOT NULL,
    hours_worked NUMERIC(5,2) NOT NULL CHECK (hours_worked >= 0), -- 支持小数工时，比如4.5小时
    PRIMARY KEY (employee_id, work_date) -- 确保一个员工一天仅一条记录
);

优势&常用操作 #

• 自动适配28/29/30/31天的月份，永远不用修改表结构
• 统计、查询逻辑简单直接：
  • 查Bill Gates 2023年3月总工时：

    SELECT SUM(hours_worked) AS total_hours
    FROM employee_work_hours
    WHERE employee_id = 1 
      AND work_date BETWEEN '2023-03-01' AND '2023-03-31';
    

  • 查所有员工3月1日的工时：

    SELECT e.name, ewh.hours_worked
    FROM employee_work_hours ewh
    JOIN employees e ON ewh.employee_id = e.id
    WHERE work_date = '2023-03-01';
    

• 扩展方便，要加加班标识、工作备注等字段直接新增列即可

2. JSONB存储（特殊场景备选） #

如果业务有特殊需求（比如需要快速导出整月数据），可以用PostgreSQL的JSONB类型存储整月工时：

CREATE TABLE employee_monthly_hours (
    employee_id INT REFERENCES employees(id),
    year_month VARCHAR(7) NOT NULL, -- 格式如'2023-03'
    daily_hours JSONB NOT NULL, -- 存储格式：{"2023-03-01":6, "2023-03-02":7,...}
    PRIMARY KEY (employee_id, year_month)
);

插入&统计示例 #

-- 插入数据
INSERT INTO employee_monthly_hours (employee_id, year_month, daily_hours)
VALUES (1, '2023-03', '{"2023-03-01":6, "2023-03-02":7, "2023-03-03":6}');

-- 统计3月总工时
SELECT employee_id, SUM((jsonb_each_text(daily_hours)).value::NUMERIC) AS total_hours
FROM employee_monthly_hours
WHERE year_month = '2023-03'
GROUP BY employee_id;

注意：这种方式查询性能不如范式化设计，仅适合特定场景。

总结 #

优先选择范式化单表设计，它既解决了月份天数的适配问题，又让数据维护、查询统计更高效。JSONB方案作为备选，仅在特殊业务需求下使用。

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