PostgreSQL CASE表达式与CROSSTAB应用咨询（含表A结构）

我来帮你梳理下如何用PostgreSQL的CASE表达式和CROSSTAB来处理你的表A数据，结合你的数据结构举几个实用的例子（先提个小建议：你的字段amount spent包含空格，在SQL中需要用双引号包裹，更推荐重命名为amount_spent来避免语法问题，下面示例我会用"amount spent"来适配你的表结构）：

一、使用CASE表达式做数据分类与聚合 #

CASE是PostgreSQL里非常灵活的条件判断工具，适合做数据分组、分类标记或条件计算。

1. 给消费金额划分档位标记 #

如果你想给每笔消费打上“低/中/高额”的分类标签，可以这样写：

SELECT
  user_id,
  timestamp,
  "amount spent",
  -- 先把带逗号的金额转成数值型，再判断档位
  CASE
    WHEN REPLACE("amount spent", ',', '.')::numeric < 20 THEN '低额消费'
    WHEN REPLACE("amount spent", ',', '.')::numeric BETWEEN 20 AND 100 THEN '中额消费'
    ELSE '高额消费'
  END AS spending_category
FROM A;

这里用REPLACE把金额里的逗号替换成小数点，再转成numeric类型才能做数值比较，最后通过CASE生成分类字段。

2. 按用户统计各档位消费次数 #

如果想统计每个用户不同档位的消费次数，可以结合GROUP BY和CASE实现：

SELECT
  user_id,
  -- 统计低额消费次数：符合条件返回1，否则返回NULL，COUNT会忽略NULL
  COUNT(CASE WHEN REPLACE("amount spent", ',', '.')::numeric < 20 THEN 1 END) AS low_spend_count,
  COUNT(CASE WHEN REPLACE("amount spent", ',', '.')::numeric BETWEEN 20 AND 100 THEN 1 END) AS mid_spend_count,
  COUNT(CASE WHEN REPLACE("amount spent", ',', '.')::numeric > 100 THEN 1 END) AS high_spend_count
FROM A
GROUP BY user_id;

二、使用CROSSTAB做数据透视（行列转换） #

CROSSTAB是PostgreSQL的tablefunc扩展提供的工具，专门用来实现行列转换（透视表）。首先需要确保扩展已安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

1. 按用户透视每日总消费 #

比如想把日期作为列，查看每个用户在不同日期的总消费金额，可以这样写：

SELECT *
FROM CROSSTAB(
  -- 第一个参数：源查询，按用户和日期分组计算总消费
  $$
  SELECT
    user_id,
    -- 提取日期部分并转成DATE类型
    TO_DATE(SUBSTRING(timestamp FROM 1 FOR 10), 'DD.MM.YYYY') AS spend_date,
    SUM(REPLACE("amount spent", ',', '.')::numeric) AS total_spent
  FROM A
  GROUP BY user_id, spend_date
  ORDER BY user_id, spend_date
  $$,
  -- 第二个参数：指定要作为列的所有日期值
  $$
  SELECT DISTINCT TO_DATE(SUBSTRING(timestamp FROM 1 FOR 10), 'DD.MM.YYYY')
  FROM A
  ORDER BY 1
  $$
) AS ct(
  -- 定义透视后的表结构：第一列是user_id，后面是各个日期列
  user_id INT,
  "2018-03-22" NUMERIC,
  "2018-03-24" NUMERIC,
  "2018-04-01" NUMERIC,
  "2018-04-02" NUMERIC,
  "2018-04-03" NUMERIC
);

执行后会得到一个透视表，每一行是一个用户，每一列对应一个日期的总消费。

2. 结合CASE和CROSSTAB，透视每日各档位订单数 #

如果想统计每天不同消费档位的订单数量，可以先通过CASE生成分类，再用CROSSTAB透视：

SELECT *
FROM CROSSTAB(
  $$
  SELECT
    TO_DATE(SUBSTRING(timestamp FROM 1 FOR 10), 'DD.MM.YYYY') AS spend_date,
    CASE
      WHEN REPLACE("amount spent", ',', '.')::numeric < 20 THEN '低额消费'
      WHEN REPLACE("amount spent", ',', '.')::numeric BETWEEN 20 AND 100 THEN '中额消费'
      ELSE '高额消费'
    END AS spending_category,
    COUNT(*) AS order_count
  FROM A
  GROUP BY spend_date, spending_category
  ORDER BY spend_date, spending_category
  $$,
  $$
  SELECT DISTINCT
    CASE
      WHEN REPLACE("amount spent", ',', '.')::numeric < 20 THEN '低额消费'
      WHEN REPLACE("amount spent", ',', '.')::numeric BETWEEN 20 AND 100 THEN '中额消费'
      ELSE '高额消费'
    END AS spending_category
  FROM A
  ORDER BY 1
  $$
) AS ct(
  spend_date DATE,
  "低额消费" INT,
  "中额消费" INT,
  "高额消费" INT
);

这个查询会把每天的各档位订单数转成列，方便直观对比每日的消费结构。

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