sql - CASE 和 WHEN SQL

Question

我有客户购买的交易数据。我尝试从过去 1 个月中选择 customer_id 并计算新近度作为客户购买的平均天数 (AVG(gap))

SELECT
    customer_id,
    (
        CASE WHEN day::DATE<= '2015-05-01'::DATE AND day::DATE > '2015-05-01'::DATE - INTERVAL '1 month' 
            THEN
            (
                SELECT
                    AVG(gap)
                FROM
                (
                    SELECT
                        customer_id,
                        ( day- LAG(day) OVER ( PARTITION BY customer_id ORDER BY day ) ) AS gap
                    FROM
                        baskets
                    JOIN
                        basket_lines
                    USING
                        ( basket_id )
                    GROUP BY 1
                ) a
            ) b
            ELSE 0
        ) AS A
FROM
    baskets
JOIN
    basket_lines
USING
    (basket_id)
GROUP BY
    1;

但是，我有一个像`

这样的错误

 ERROR:  syntax error at or near "b"
 LINE 45:    GROUP BY 1)a)b ELSE 0) AS A
                          ^

这是否意味着我不能在 THEN 语句之后使用子查询？

Answer 1

THEN 子句中的子查询不使用别名。此外，您必须以 END 结束您的 CASE 表达式:

SELECT
    customer_id,
    (CASE WHEN day::DATE<= '2015-05-01'::DATE AND
               day::DATE > '2015-05-01'::DATE - INTERVAL '1 month' 
          THEN
          (SELECT AVG(gap) FROM (
               SELECT customer_id,
                   (day- LAG(day) OVER (PARTITION BY customer_id ORDER BY day)) as gap
               FROM baskets
               JOIN basket_lines
               USING (basket_id)
               GROUP BY 1) a) ELSE 0 END) AS A
FROM baskets
JOIN basket_lines
USING (basket_id)
GROUP BY 1;

但是您的 select 语句中有一个相关的子查询。这可能不是最优的，我们可能会使用联接重写您的查询。

我建议进行以下重构:

WITH cte AS (
    SELECT
        customer_id,
        (day- LAG(day) OVER (PARTITION BY customer_id ORDER BY day)) as gap
    FROM baskets
    INNER JOIN basket_lines
    USING (basket_id)
    WHERE day::DATE<= '2015-05-01'::DATE AND
          day::DATE > '2015-05-01'::DATE - INTERVAL '1 month' 
)

SELECT
    customer_id,
    AVG(gap) AS cust_avg
FROM cte
GROUP BY
    customer_id;