如何按组插入含常量值的行？（PrestoDB/PostgreSQL）

原始数据 #

name       time                        lifetime_visit_at_hospital
    jack       2022-12-02 03:25:00.000     1
    jack       2022-12-02 03:33:00.000     2
    jack       2022-12-03 01:13:00.000     3
    jack       2022-12-03 01:15:00.000     4
    jack       2022-12-04 00:52:00.000     5
    amanda     2017-01-01 05:03:00.000     1
    amanda     2020-02-13 00:36:00.000     2
    amanda     2020-02-13 00:38:00.000     3
    amanda     2020-02-13 00:39:00.000     4
    amanda     2020-02-13 00:40:00.000     5
    amanda     2020-02-14 10:23:00.000     6
    amanda     2020-03-30 03:13:00.000     7
    amanda     2020-09-25 17:17:00.000     8
    amanda     2020-12-23 19:39:00.000     9
    amanda     2021-05-19 20:19:00.000     10
    sam        2023-01-26 23:13:00.000     1
    sam        2023-02-12 17:35:00.000     2
    sam        2023-02-12 17:37:00.000     3
    sam        2023-02-12 17:40:00.000     4
    sam        2023-02-13 16:10:00.000     5
    sam        2023-02-16 19:37:00.000     6
    sam        2023-02-18 16:26:00.000     7

需求说明 #

需要为每个name分组添加一行初始化数据：

• time字段固定为1900-01-01 00:00:00.000
• lifetime_visit_at_hospital字段值为0
  添加后可确保后续表格顺序混乱时，仍能通过name和lifetime_visit_at_hospital正常排序。

解决方案 #

通用实现逻辑 #

通过UNION ALL合并两部分数据：

1. 从原表提取所有唯一name，构造对应的初始化行
2. 原表的全部原始数据
   最后按name和lifetime_visit_at_hospital排序，保证初始化行在每个用户分组的最前面。

PrestoDB/PostgreSQL 查询语句 #

WITH unique_names AS (
    SELECT DISTINCT name
    FROM your_table_name  -- 替换为实际表名
)
SELECT name, 
       TIMESTAMP '1900-01-01 00:00:00.000' AS time,
       0 AS lifetime_visit_at_hospital
FROM unique_names
UNION ALL
SELECT name, time, lifetime_visit_at_hospital
FROM your_table_name  -- 替换为实际表名
ORDER BY name, lifetime_visit_at_hospital;

说明 #

• unique_names公共表表达式用于获取所有不重复的用户名
• 第一部分查询为每个用户生成初始化行
• UNION ALL避免去重操作，保证原始数据完整保留
• 最终排序规则确保每个用户的初始化行排在最前，后续数据按原有就诊顺序排列

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