SQL Server 从列中选择出现次数最多的项，基于日期，频率相同

db2dz4w8 于 2022-11-21 发布在其他

关注(0)|答案(1)|浏览(151)

假设 ID = {1 ， [ ... ] ，5} 且 Col1 = {a ， b ， c ， Null} ，我有以下数据集：
| 识别码|列 1|日期|
| - -| - -| - -|
| 一个|一种|2022 年 1 月 10 日|
| 一个|一种|2022 年 2 月 10 日|
| 一个|一种|2022 年 3 月 10 日|
| 2 个|最佳|2022 年 1 月 10 日|
| 2 个|C 语言|2022 年 2 月 10 日|
| 2 个|C 语言|2022 年 3 月 10 日|
| 三个|一种|2022 年 1 月 10 日|
| 三个|最佳|2022 年 2 月 10 日|
| 三个|空值|2022 年 3 月 10 日|
| 四个|C 语言|2022 年 1 月 10 日|
| 五个|最佳|2022 年 1 月 10 日|
| 五个|空值|2022 年 2 月 10 日|
| 五个|空值|2022 年 3 月 10 日|
我想按 ID 对行进行分组，计算新列以显示出现的次数，并计算一个新列以显示字符串，这取决于 Col1 的频率。大多数 a = Hi ，大多数 b = Hello ，大多数 c = Welcome ，大多数 Null = Unknown 。如果除 Null 之外的多个模态具有相同的频率，则基于日期的最近模态获胜。
下面是我需要的数据集：
| 识别码|nb _ a| nb _ b| nb _ c| nb _ 空值|最大的|
| - -| - -| - -| - -| - -| - -|
| 一个|三个|第 0 页|第 0 页|第 0 页|你好|
| 2 个|第 0 页|一个|2 个|第 0 页|欢迎光临|
| 三个|一个|一个|第 0 页|一个|你好|
| 四个|第 0 页|第 0 页|一个|第 0 页|欢迎光临|
| 五个|第 0 页|一个|第 0 页|2 个|不详|
我必须在 Dataiku 的一个计算方法中完成这个操作。 group by 由方法的 group by 部分处理，而查询的其余部分需要在方法的 " 自定义聚合 " 部分中完成。我在代码的 if equality then most recent 部分遇到了麻烦。
我的 SQL 代码如下所示：

CASE WHEN SUM(CASE WHEN Col1 = a THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = b THEN 1 ELSE 0)
      AND SUM(CASE WHEN Col1 = a THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = c THEN 1 ELSE 0)
     THEN 'Hi'

CASE WHEN SUM(CASE WHEN Col1 = b THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = a THEN 1 ELSE 0)
      AND SUM(CASE WHEN Col1 = b THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = c THEN 1 ELSE 0)
     THEN 'Hello'

CASE WHEN SUM(CASE WHEN Col1 = c THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = a THEN 1 ELSE 0)
      AND SUM(CASE WHEN Col1 = c THEN 1 ELSE 0) >
          SUM(CASE WHEN Col1 = b THEN 1 ELSE 0)
     THEN 'Welcome'

中的每一个
等等等等，对其他情况重复。
但肯定有更好的方法来做这件事吧？而且我不知道如何在频率相同的情况下包括最近的一个。
谢谢你的帮助，如果我的信息不清楚，我很抱歉。

sql-server

来源：https://stackoverflow.com/questions/73946902/select-the-greatest-occurence-from-a-column-based-on-date-is-frequencies-are-th

1条答案

按热度按时间

mftmpeh81#

我试着在Azure Synapse中使用SQL脚本来重现这一点。

创建的样本表如下图所示。

Create  table tab1 (id int, col1 varchar(50), date_column date)
Insert  into tab1 values(1,'a','2021-10-01')
Insert  into tab1 values(1,'a','2021-10-02')
Insert  into tab1 values(1,'a','2021-10-03')
Insert  into tab1 values(2,'b','2021-10-01')
Insert  into tab1 values(2,'c','2021-10-02')
Insert  into tab1 values(2,'c','2021-10-03')
Insert  into tab1 values(3,'a','2021-10-01')
Insert  into tab1 values(3,'b','2021-10-02')
Insert  into tab1 values(3,'Null','2021-10-03')
Insert  into tab1 values(4,'c','2021-10-01')
Insert  into tab1 values(5,'b','2021-10-01')
Insert  into tab1 values(5,'Null','2021-10-02')
Insert  into tab1 values(5,'Null','2021-10-03')

步骤：1

编写查询是为了查找组id col1中的值计数以及id col1的每个组合中的最大日期值。

select
distinct id,col1,
count(*) over (partition by id,col1) as count,
case when col1='Null' then null else max(date_column) over (partition by id,col1) end as max_date
from tab1

步骤：2

在每个id，col1组中按count和max_date列的降序计算行号。当两个或多个值具有相同的频率时，将执行此操作，然后根据最新日期分配值。

select *, row_number() over (partition by id order by count desc, max_date desc) as row_num from
(select
distinct id,col1,
count(*) over (partition by id,col1) as count,
case when col1='Null' then null else max(date_column) over (partition by id,col1) end as max_date
from tab1)q1

步骤：3

筛选row_num=1的行项目，并使用逻辑*most a = Hi，most b = Hello，most c = Welcome，most Null = Unknown*为最大列分配值。

完整查询

select id, 
[greatest]=case when col1='a' then 'Hi'
                when col1='b' then 'Hello'
                when col1='c' then 'Welcome'
                else 'Unknown'
                end 
from
(select *, row_number() over (partition by id order by count desc, max_date desc) as row_num from
(select
distinct id,col1,
count(*) over (partition by id,col1) as count,
case when col1='Null' then null else max(date_column) over (partition by id,col1) end as max_date
from tab1)q1
)q2 where row_num=1

输出

通过这种方法，即使频率相同，也可以根据最近的日期更新所需的值。

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 从列中选择出现次数最多的项，基于日期，频率相同

1条答案

相关问题

热门标签

最新问答