Sum（Case when）导致选择的多行 [英] Sum(Case when) resulting in multiple rows of the selection

问题描述

我有一张庞大的客户订单表，我想运行一个查询以按 user_id在过去13个月中按月列出订单。我现在（在下面）的工作方式，但不是每个user_id仅列出一行，它还为user_id的每个订单列出了一行。例如：一位用户在我们的一生中总共收到42笔订单，因此它在42行中列出了他的user_id，每行只有一笔付款。通常情况下，我会将其放在excel的数据透视表中，但是我已超过了百万行的限制，因此我需要它是正确的并且成功为零。我希望读取的内容看起来像这样：

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 | jul_12 | aug_12 |

user_id | jul_12 | aug_12 |

123456 | 150.00 | 150.00 |

123456 | 150.00 | 150.00 |

不是这样的：

user_id | jul_12 | aug_12 |

user_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 |

等另外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;

推荐答案

尝试类似的操作：

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分组，因此每个订单只获得一行。 和每个订单的时间戳都不同。

You were getting one row per order because you were grouping by o.time_stamp and timestamps are different for each order.

查询的较短版本：

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;

这篇关于Sum（Case when）导致选择的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！