PostgreSQL按document_id分组获取最新修改时间的所有记录

原始查询语句 #

select ofd.document_id, ofd.last_modified_time , xmlt.*
from orbeon_form_data ofd
cross join lateral
XMLTABLE
(
 '/form/section-uczestnicy/section-uczestnicy-iteration/section-uczestnik/section-dane-uczestnika'
 PASSING by ref "xml" 
 columns
  "control-create" text path '/form/teryt-metadata/control-created',
  "app-signature" text path '/form/section-informacje-o-projekcie/control-sygnatura-nawa',
  "control-sygnatura" text path '/form/section-informacje-o-projekcie/control-numer-projektu',
  "control-inst" text path '/form/section-instytucja/section-instytucjaj-dane-podstawowe/control-inst-nazwa',
  "control-imie" text PATH 'control-imie',
  "control-nazwisko" text PATH 'control-nazwisko'
) xmlt
where ofd.form='Monitorowanie_Uczestnikow' and ofd.form_version=1 
and ofd.document_id in ('0ea8329ff9efe8a3052264fa494243b6323ae433', '0ea8329ff9efe8a3052264fa494243b6323ae434')
order by ofd.document_id, ofd.last_modified_time desc;

查询返回结果 #

需求说明 #

按document_id分组，筛选每个分组内last_modified_time最新的所有记录：

• document_id为0ea8329ff9efe8a3052264fa494243b6323ae433的两条最新记录（时间均为2018-12-13 09:12:24.179）
• document_id为0ea8329ff9efe8a3052264fa494243b6323ae434的最新记录（时间为2018-12-13 09:12:24.179）

此前尝试按document_id分组取max(last_modified_time)，或使用DISTINCT ON (document_id)结合排序，仅能得到每个document_id的单条记录，无法满足需求。

解决方案 #

方法一：使用窗口函数RANK() #

通过窗口函数按document_id分区，对last_modified_time降序排名，筛选排名为1的记录（同时间的记录会获得相同排名）：

WITH ranked_records AS (
    SELECT 
        ofd.document_id, 
        ofd.last_modified_time, 
        xmlt.*,
        RANK() OVER (PARTITION BY ofd.document_id ORDER BY ofd.last_modified_time DESC) AS rnk
    FROM orbeon_form_data ofd
    CROSS JOIN LATERAL XMLTABLE(
        '/form/section-uczestnicy/section-uczestnicy-iteration/section-uczestnik/section-dane-uczestnika'
        PASSING by ref "xml" 
        columns
            "control-create" text path '/form/teryt-metadata/control-created',
            "app-signature" text path '/form/section-informacje-o-projekcie/control-sygnatura-nawa',
            "control-sygnatura" text path '/form/section-informacje-o-projekcie/control-numer-projektu',
            "control-inst" text path '/form/section-instytucja/section-instytucjaj-dane-podstawowe/control-inst-nazwa',
            "control-imie" text PATH 'control-imie',
            "control-nazwisko" text PATH 'control-nazwisko'
    ) xmlt
    WHERE ofd.form='Monitorowanie_Uczestnikow' 
      AND ofd.form_version=1 
      AND ofd.document_id IN ('0ea8329ff9efe8a3052264fa494243b6323ae433', '0ea8329ff9efe8a3052264fa494243b6323ae434')
)
SELECT document_id, last_modified_time, "control-create", "app-signature", "control-sygnatura", "control-inst", "control-imie", "control-nazwisko"
FROM ranked_records
WHERE rnk = 1
ORDER BY document_id, last_modified_time DESC;

方法二：子查询获取最大时间后关联 #

先查询每个document_id对应的最新last_modified_time，再关联原查询结果筛选符合条件的记录：

WITH latest_times AS (
    SELECT 
        document_id, 
        MAX(last_modified_time) AS latest_time
    FROM orbeon_form_data
    WHERE form='Monitorowanie_Uczestnikow' 
      AND form_version=1 
      AND document_id IN ('0ea8329ff9efe8a3052264fa494243b6323ae433', '0ea8329ff9efe8a3052264fa494243b6323ae434')
    GROUP BY document_id
)
SELECT 
    ofd.document_id, 
    ofd.last_modified_time, 
    xmlt.*
FROM orbeon_form_data ofd
JOIN latest_times lt ON ofd.document_id = lt.document_id AND ofd.last_modified_time = lt.latest_time
CROSS JOIN LATERAL XMLTABLE(
    '/form/section-uczestnicy/section-uczestnicy-iteration/section-uczestnik/section-dane-uczestnika'
    PASSING by ref "xml" 
    columns
        "control-create" text path '/form/teryt-metadata/control-created',
        "app-signature" text path '/form/section-informacje-o-projekcie/control-sygnatura-nawa',
        "control-sygnatura" text path '/form/section-informacje-o-projekcie/control-numer-projektu',
        "control-inst" text path '/form/section-instytucja/section-instytucjaj-dane-podstawowe/control-inst-nazwa',
        "control-imie" text PATH 'control-imie',
        "control-nazwisko" text PATH 'control-nazwisko'
) xmlt
WHERE ofd.form='Monitorowanie_Uczestnikow' 
  AND ofd.form_version=1
ORDER BY ofd.document_id, ofd.last_modified_time DESC;

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