代码之家 › 专栏 › 技术社区 › psrpsrpsr

如何将多个组压缩为一个?

netezza postgresql sql

psrpsrpsr · 技术社区 · 7 年前

我有一个简单的数据集,我想为在左连接中有任何匹配行的user\u id标记为“曾经”采取了行动,并通过扩展标记那些“从未”采取行动。

有关样本数据和查询,请参阅rextester: http://rextester.com/BOVT75663

访问次数:

| loc_id | user_id | site_visit_date | site_visit_count |
|--------|---------|-----------------|------------------|
| 1234   | 003     | 06/05/2016      | 1                |
| 1234   | 003     | 06/06/2016      | 1                |
| 1234   | 003     | 06/09/2016      | 1                |
| 1234   | 802     | 05/18/2016      | 1                |
| 1234   | 818     | 02/19/2016      | 1                |
| 1234   | 818     | 02/21/2016      | 1                |
...and so on

| loc_id | user_id | action_date | action_category | action_count |
|--------|---------|-------------|-----------------|--------------|
| 1234   | 003     | 06/05/2016  | action123       | 2            |
| 1234   | 003     | 06/14/2016  | action123       | 5            |
| 1234   | 003     | 07/01/2016  | action123       | 1            |
| 1234   | 868     | 02/29/2016  | action123       | 13           |
| 1234   | 868     | 03/17/2016  | action123       | 9            |
| 1234   | 877     | 02/08/2016  | action123       | 5            |
| 1234   | 877     | 03/25/2016  | action123       | 4            |

| user_id | ever_never | sum  |
|---------|------------|------|
| 3       | ever       | 7    |
| 802     | never      | NULL |
| 818     | never      | NULL |
| 868     | ever       | 22   |
| 871     | never      | NULL |
| 876     | never      | NULL |
| 877     | ever       | 9    |

当前输出/查询:

| user_id | ever_never | sum  |
|---------|------------|------|
| 3       | ever       | 7    |
| 3       | never      | NULL |
| 802     | never      | NULL |
| 818     | never      | NULL |
| 868     | ever       | 22   |
| 868     | never      | NULL |
| 871     | never      | NULL |
| 876     | never      | NULL |
| 877     | ever       | 9    |
| 877     | never      | NULL |

with tbl as (
select
    v.loc_id
    ,v.user_id
    ,TO_CHAR(v.site_visit_date,'YYYY-MM-DD')
    ,v.site_visit_count
    ,TO_CHAR(a.action_date,'YYYY-MM-DD')
    ,a.action_category
    ,a.action_count
    ,case when a.action_count >=1 then 'ever' else 'never' end ever_never
from
    visits v
    left join actions a on v.user_id = a.user_id and v.site_visit_date = a.action_date
order by 1,2,5
)
select
    user_id
    ,ever_never
    ,sum(action_count)
from
    tbl
group by
    user_id
    ,ever_never
order by 1,2

2 回复 | 直到 7 年前

TheEsnSiavashi 7 年前

您可以使用 MIN ever ,当还有 never

with tbl as (
select
   v.loc_id
   ,v.user_id
   ,TO_CHAR(v.site_visit_date,'YYYY-MM-DD')
   ,v.site_visit_count
   ,TO_CHAR(a.action_date,'YYYY-MM-DD')
   ,a.action_category
   ,a.action_count
   ,case when a.action_count >=1 then 'ever' else 'never' end ever_never
from
visits v
left join actions a on v.user_id = a.user_id and v.site_visit_date = a.action_date
order by 1,2,5
)
select
   user_id
   ,MIN(ever_never)
   ,sum(action_count)
from
   tbl
group by
   user_id
order by 1,2

检查结果: http://rextester.com/live/LQDR42614

Gordon Linoff 7 年前

去除 ever_never 从上一个 group by

select user_id, min(ever_never) as ever_never, sum(action_count)
from tbl
group by user_id
order by 1