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 |7 月 12 日 |8 月 12 日 |

user_id | jul_12 | aug_12 |

123456 |150.00 |150.00 |

123456 | 150.00 | 150.00 |

不是这个:

user_id |7 月 12 日 |8 月 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.time_stamp 进行分组，并且每个订单的时间戳不同，所以每个订单只有一行.

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屋！