首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将最频繁的值与组聚为

问将最频繁的值与组聚为
EN

Stack Overflow用户
提问于 2020-01-04 01:54:02
回答 4查看 722关注 0票数 0

我有一张这样的桌子:

代码语言:javascript
复制
PARTNUMBER | QUANTITY | DESCRIPTION
'foo'        2          'a'
'foo'        2          'a1'
'bar'        2          'b'
'bar'        2          'b'
'bar'        2          'b1'
'bizz'       2          'c'

我试图按PARTNUMBER进行分组,按数量进行聚合,并根据最常见的外观进行聚合描述。

我尝试使用子查询来聚合最频繁出现的描述,但我在正确处理它时遇到了一些困难,特别是使用GROUP BY。

以下是我所拥有的:

代码语言:javascript
复制
SELECT SUM(QUANTITY) AS QUANTITY, PARTNNUMBER, 
    (SELECT TOP(1) [DESCRIPTION]
    FROM [PBJobDB].[dbo].[DEVICES]
    /*WHERE DESCRIPTION = t1.PARTNO ?? */
    GROUP BY [DESCRIPTION], PARTNNUMBER
    ORDER BY COUNT([DESCRIPTION]) DESC) as [DESCRIPTION]
FROM `database.table`
GROUP BY PARTNUMBER, [DESCRIPTION]

子查询不是由PARTNUMBER获取最频繁的描述,而是在整个表中给出最频繁的描述。

我希望输出结果如下:

代码语言:javascript
复制
PARTNUMBER | QUANTITY | DESCRIPTION
'foo'        4          'a'
'bar'        6          'b'
'bizz'       2          'c'
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2020-01-04 08:02:19

我试过了,请检查一下它是否对你有用,

代码语言:javascript
复制
SELECT PARTNUMBER,SUM(QUANTITY) AS QUANTITY,  
(
    SELECT TOP 1 DESCP FROM
        (SELECT [DESCRIPTION]'DESCP',COUNT(*)'CNT'
        FROM testtable
        WHERE PARTNUMBER = t1.PARTNUMBER
        GROUP BY [DESCRIPTION]) A 
    GROUP BY DESCP,CNT HAVING CNT=MAX(CNT) 
)as [DESCRIPTION]
FROM testtable  T1
GROUP BY PARTNUMBER
票数 2
EN

Stack Overflow用户

发布于 2020-01-04 01:54:02

这就是最后为我工作的..。

代码语言:javascript
复制
select distinct t1.PARTNUMBER , sum(t1.QUANTITY) AS QUANTITY, (
    select TOP(1) [DESCRIPTION]
    from [PBJobDB].[dbo].[DEVICES] AS t2
    where t2.PARTNUMBER = t1.PARTNUMBER 
    group by [DESCRIPTION]
    order by count(*) desc ) as [DESCRIPTION] 
from `database.table` AS t1 
/* WHERE `column` IS NULL AND `other_column` = 'some_value' */ 
GROUP BY t1.PARTNUMBER
票数 0
EN

Stack Overflow用户

发布于 2020-01-04 03:28:00

你在寻找模式。我将使用两个级别的聚合:

代码语言:javascript
复制
select partnumber, sum(quantity) as total_quantity,
       max(case when seqnum = 1 then description end) as description
from (select partnumber, description, sum(quantity) as quantity,
             row_number() over (partition by partnumber order by sum(quantity) desc, description) as seqnum
      from t
      group by partnumber, description
     ) pd
group by partnumber;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/59587299

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档