当前位置：文江博客话题详情

有没有办法使用Groupby函数来收集数据并在一次通过中检查

发布于 2025-01-23 07:06:06 字数 672 浏览 3 评论 0原文

table_1的示例数据

有此查询以返回选择顾客，总和（'a'，'b'中的活动时的情况然后1 else 0 end）为num_activity_a_or_b 来自Table_1 小组通过客户

结果：

想要将其扩展为再返回一列，如果给定代码说X1如果活动为“ A”和“ C”，则返回num_of_a_and_c_c_activity。

有点卡住了如何在一次通行证中收集和无视代码和活动。我们可以结合窗口功能以实现这一目标吗？

请建议和帮助

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦旅人picnic 2025-01-30 07:06:06

更新：
根据更新的结果，也许以下查询是您需要的
因此，我认为您也需要A和C以及X1。
因此，我计算出不同的活动，即A和C，然后将整数除以2。如果仅存在A，则在整数划分中计数= 1但1/2 = 0。
当存在A和C时，只有1个。

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   COUNT(DISTINCT CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN activity 
      ELSE NULL 
   END)/2 AS num_activity_a_and_c
from table_1 
group by customer

也许您的查询可能是

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   SUM(CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN 1 
      ELSE 0 
   END) AS num_activity_a_or_c
from table_1 
group by customer

UPDATE:
based on the updated results, maybe the below query is what you need
So what i assume is that you need both a and c as well x1 .
So I count distinct activities which are a and c and then do integer division by 2. if only a is present then count distinct =1 but 1/2 =0 in integer division.
It is only 1 when both a and c are present.

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   COUNT(DISTINCT CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN activity 
      ELSE NULL 
   END)/2 AS num_activity_a_and_c
from table_1 
group by customer

Maybe your query can be

select 
 customer, 
  SUM(CASE WHEN activity IN ( 'a','b') 
     THEN 1 
     ELSE 0 
   END) AS num_activity_a_or_b,
   SUM(CASE WHEN code IN ('x1') AND activity IN ( 'a','c') 
     THEN 1 
      ELSE 0 
   END) AS num_activity_a_or_c
from table_1 
group by customer

回复收藏 0 原文

~没有更多了~