基于多条件过滤时序交易数据的SQL实现问询

一、优化原有物化视图方案 #

你的原有方案失效是因为交叉连接的物化视图仅能处理单交易类型条件，调整物化视图存储每个客户各交易类型的最新交易记录，即可支持多条件组合查询：

1. 创建优化后的物化视图 #

CREATE MATERIALIZED VIEW mv_customer_latest_transactions AS
SELECT customer_id, transaction_type, transaction_date AS latest_date, transaction_value AS latest_value
FROM (
    SELECT 
        customer_id, transaction_type, transaction_date, transaction_value,
        ROW_NUMBER() OVER (PARTITION BY customer_id, transaction_type ORDER BY transaction_date DESC) AS rn
    FROM transactions
) t
WHERE rn = 1;

这个视图会保留每个客户每种交易类型的最新一条记录，后续查询无需重复计算最新交易。

2. 多条件查询示例 #

以筛选"最新buy金额>90且sell金额>100"的客户及对应记录为例：

-- 先筛选符合条件的客户
WITH qualified_customers AS (
    SELECT buy.customer_id
    FROM mv_customer_latest_transactions buy
    JOIN mv_customer_latest_transactions sell 
        ON buy.customer_id = sell.customer_id
    WHERE buy.transaction_type = 'buy' AND buy.latest_value > 90
      AND sell.transaction_type = 'sell' AND sell.latest_value > 100
)
-- 关联原表获取对应交易记录（如果只需要最新记录，直接从物化视图取即可）
SELECT t.*
FROM transactions t
JOIN qualified_customers qc ON t.customer_id = qc.customer_id
-- 若仅需客户的最新交易记录，替换为关联物化视图
-- JOIN mv_customer_latest_transactions mv ON t.customer_id = mv.customer_id AND t.transaction_type = mv.transaction_type AND t.transaction_date = mv.latest_date;

新增条件时，只需在qualified_customers中加入对应交易类型的视图JOIN和过滤规则即可。

二、无需物化视图的实时查询方案（推荐） #

如果条件是动态变化的，实时查询方案更灵活，无需维护物化视图的刷新逻辑：

方法1：条件聚合筛选客户 #

通过窗口函数先标记每个客户各交易类型的最新记录，再用条件聚合筛选符合要求的客户：

WITH customer_latest AS (
    SELECT 
        customer_id, transaction_type, transaction_value, transaction_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id, transaction_type ORDER BY transaction_date DESC) AS rn
    FROM transactions
), qualified_customers AS (
    SELECT customer_id
    FROM customer_latest
    WHERE rn = 1
    GROUP BY customer_id
    HAVING 
        MAX(CASE WHEN transaction_type = 'buy' THEN transaction_value END) > 90
        AND MAX(CASE WHEN transaction_type = 'sell' THEN transaction_value END) > 100
        -- 新增条件直接追加AND MAX(CASE ...)，比如 AND MAX(CASE WHEN transaction_type = 'transfer' THEN transaction_value END) > 500
)
-- 获取符合条件客户的所有交易记录，或仅最新记录
SELECT t.*
FROM transactions t
JOIN qualified_customers qc ON t.customer_id = qc.customer_id
-- 若仅需最新记录，追加以下条件
-- JOIN customer_latest cl ON t.customer_id = cl.customer_id AND t.transaction_type = cl.transaction_type AND t.transaction_date = cl.transaction_date AND cl.rn = 1;

方法2：多LATERAL JOIN组合条件 #

每个交易类型条件对应一个LATERAL子查询，逻辑直观，适合动态生成SQL的场景：

WITH qualified_customers AS (
    SELECT DISTINCT c.customer_id
    FROM (SELECT DISTINCT customer_id FROM transactions) c
    -- 验证buy类型的最新交易条件
    LATERAL (
        SELECT transaction_value
        FROM transactions
        WHERE customer_id = c.customer_id AND transaction_type = 'buy'
        ORDER BY transaction_date DESC
        LIMIT 1
    ) buy_check
    -- 验证sell类型的最新交易条件
    LATERAL (
        SELECT transaction_value
        FROM transactions
        WHERE customer_id = c.customer_id AND transaction_type = 'sell'
        ORDER BY transaction_date DESC
        LIMIT 1
    ) sell_check
    WHERE buy_check.transaction_value > 90 
      AND sell_check.transaction_value > 100
      -- 新增条件时，添加对应的LATERAL子查询和WHERE过滤
)
SELECT t.*
FROM transactions t
JOIN qualified_customers qc ON t.customer_id = qc.customer_id
-- 若仅需最新交易记录，追加：
-- WHERE (t.customer_id, t.transaction_type, t.transaction_date) IN (
--     SELECT customer_id, transaction_type, MAX(transaction_date)
--     FROM transactions
--     WHERE customer_id IN (SELECT customer_id FROM qualified_customers)
--     GROUP BY customer_id, transaction_type
-- );

三、性能优化要点 #

• 创建针对性索引：CREATE INDEX idx_trans_cust_type_date ON transactions(customer_id, transaction_type, transaction_date DESC) INCLUDE (transaction_value);，这个索引能大幅加速"获取客户某交易类型最新记录"的查询。
• 若使用物化视图，可设置定期刷新（如REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_latest_transactions;），避免数据过期。
• 1万+客户的规模下，上述方案均能满足性能需求，优先推荐实时查询方案，除非对查询延迟有极致要求。

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