总和(情况)导致选择的多行[英] Sum(Case when) resulting in multiple rows of the selection

本文是小编为大家收集整理的关于总和(情况)导致选择的多行的处理方法,想解了总和(情况)导致选择的多行的问题怎么解决?总和(情况)导致选择的多行问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一张巨大的客户订单表,我想在过去13个月内按月按月列出一个查询,以列出" user_id".我现在拥有的(下)有效,但不仅仅列出每个user_id的一行,而是为每个订单列出一个行,user_id的每个顺序.例如:一个用户在他的一生中有42个总订单,因此它以42行列出了他的用户_id,而每行只有一笔付款.通常,我只会将其扔进Excel的枢轴表中,但是我超过了百万行限制,因此我需要它正确并取得了零成功.我想读出这样的读物:

user_id | JUL_12 | aug_12 |

123456 | 150.00 | 150.00 |

不是这个:

user_id | JUL_12 | aug_12 |

123456 | 0.00 | 150.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进行分组,并且每个订单的时间戳都不同.

查询的较短版本:

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;

本文地址:https://www.itbaoku.cn/post/1764198.html

相关标签/搜索