postgresql Sum(Case when) 导致选择的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18111280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum(Case when) resulting in multiple rows of the selection
提问by greenpowerade
I have a huge table of customer orders and I want to run one query to list orders by month for the past 13 months by 'user_id'. What I have now (below) works but instead of only listing one row per user_id it lists one row for each order the user_id has. Ex: one user has 42 total orders over his life with us so it lists his user_id in 42 rows and each row only has one payment. Typically I would just throw this in a pivot table in excel but I'm over the million row limit so I need for it to be right and have had zero success. I would like for the read out to look like this:
我有一张巨大的客户订单表，我想运行一个查询，通过“user_id”按月列出过去 13 个月的订单。我现在所拥有的（如下）可以工作，但不是只为每个 user_id 列出一行，而是为 user_id 的每个订单列出一行。例如：一个用户在他的一生中总共有 42 个订单，所以它在 42 行中列出了他的 user_id，每行只有一次付款。通常我会把它放在excel的数据透视表中，但我超过了百万行限制，所以我需要它是正确的并且成功率为零。我希望读出的内容如下所示：
user_id | jul_12 | aug_12 |
用户 ID | jul_12 | aug_12 |
123456 | 150.00 | 150.00 |
123456 | 150.00 | 150.00 |
Not this:
不是这个：
user_id | jul_12 | aug_12 |
用户 ID | jul_12 | aug_12 |
123456 | 0.00 | 150.00 |
123456 | 0.00 | 150.00 |
123456 | 150.00 | 0.00 |
123456 | 150.00 | 0.00 |
etc. 40 more rows
等等 40多行
SELECT ui.user_id,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 07 THEN o.amount ELSE 0 END) jul_12,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 08 THEN o.amount ELSE 0 END) aug_12,
FROM orders o JOIN users_info ui ON ui.user_id = o.user_id
WHERE user_id = '123456'
GROUP BY ui.user_id, o.time_stamp;
回答by Igor Romanchenko
Try something like:
尝试类似：
SELECT ui.user_id,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 07 THEN o.amount ELSE 0 END) jul_12,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 08 THEN o.amount ELSE 0 END) aug_12,
FROM orders o JOIN users_info ui ON ui.user_id = o.user_id
WHERE user_id = '123456'
GROUP BY ui.user_id;
You were getting one row per order because you were grouping by o.time_stampand timestamps are different for each order.
您每个订单获得一行，因为您分组依据o.time_stamp并且每个订单的时间戳不同。
A shorter version of query:
查询的较短版本：
SELECT ui.user_id,
SUM(CASE WHEN date_trunc('month', o.time_stamp) = to_date('2012 07','YYYY MM') THEN o.amount END) jul_12,
SUM(CASE WHEN date_trunc('month', o.time_stamp) = to_date('2012 08','YYYY MM') THEN o.amount END) aug_12,
FROM orders o
JOIN users_info ui ON ui.user_id = o.user_id
WHERE ui.user_id = '123456'
GROUP BY ui.user_id;
