PostgreSQL中如何存储子查询返回的日期列表以供复用

一、将查询结果存储为可复用的日期变量 #

针对你执行的查询：

select docdate from jew_sales_edw limit 10;

返回的日期集合，可以用以下方式存储为数组或变量，方便后续查询复用：

1. 存储为数据库级数组变量 #

直接通过SELECT INTO将结果转为数组并赋值：

SELECT array_agg(docdate::date) INTO my_dates_array FROM jew_sales_edw LIMIT 10;

后续查询中可直接使用该数组，比如：

SELECT * FROM your_table WHERE date_column = ANY(my_dates_array);

2. psql会话级变量（命令行环境） #

如果在psql客户端操作，可通过\gset将结果转为会话变量：

SELECT array_agg(docdate::date) AS my_dates FROM jew_sales_edw LIMIT 10 \gset

之后用:my_dates引用变量：

SELECT * FROM your_table WHERE date_column = ANY(:my_dates);

3. 临时表存储（适合跨会话或大数据量） #

创建临时表存储日期集合，支持更复杂的关联查询：

CREATE TEMP TABLE temp_dates AS
SELECT docdate::date FROM jew_sales_edw LIMIT 10;

复用示例：

SELECT * FROM your_table st
JOIN temp_dates td ON st.date_column = td.docdate;

二、处理UPDATE语句中的多结果子查询 #

你的UPDATE语句中，子查询select (docdate::date + integer '21') from jew_sales_edw where created_date=CURRENT_DATE会返回多个值，直接使用会报错，以下是两种解决方式：

方案1：先存储为数组再使用 #

通过CTE将子查询结果转为数组，再在WHERE条件中匹配：

WITH target_date_range AS (
    SELECT array_agg((docdate::date + 21)) AS target_dates
    FROM jew_sales_edw 
    WHERE created_date = CURRENT_DATE
)
UPDATE public.transaction_consolidated_temp X
SET anniversary_tag = 'Y'
FROM (
    SELECT acc.ucic__c, mdm.cust_unq_id
    FROM salesforce.account acc
    INNER JOIN public.mdm_cr_edw mdm ON mdm.ucic::varchar = acc.ucic__c
    CROSS JOIN target_date_range dr
    WHERE DATE_PART('doy', acc.person_date_of_anniversary__c) <= ANY(dr.target_dates)
) Y
WHERE X.cust_unq_id = Y.cust_unq_id
  AND X.docdate::date = '2022-06-01'::date;

方案2：直接用ANY()包裹子查询（无需显式变量） #

省去变量存储步骤，直接在WHERE条件中用ANY()处理多结果子查询：

UPDATE public.transaction_consolidated_temp X
SET anniversary_tag = 'Y'
FROM (
    SELECT acc.ucic__c, mdm.cust_unq_id
    FROM salesforce.account acc
    INNER JOIN public.mdm_cr_edw mdm ON mdm.ucic::varchar = acc.ucic__c
    WHERE DATE_PART('doy', acc.person_date_of_anniversary__c) <= ANY(
        SELECT (docdate::date + 21)
        FROM jew_sales_edw 
        WHERE created_date = CURRENT_DATE
    )
) Y
WHERE X.cust_unq_id = Y.cust_unq_id
  AND X.docdate::date = '2022-06-01'::date;

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