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

时间间隔内的T-SQL计数并找到最高频率

tsql sql-server sql

default_settings · 技术社区 · 7 年前

我必须构建查询,它显示和统计事务数据中最频繁的产品。在这种情况下,我们可以在一天内拥有一个产品的多行,但是查询应该考虑到整个数据。

例如,从下面的数据中,我们可以看到,产品“平板电脑”是最受欢迎的产品,销量最大。不过,我们也可以观察到,这一产品在7天内只售出了2天。

另一方面,从数据的开始到结束,“清洁剂”产品每天都在销售。

在这种情况下,外观的总和是不起作用的,因为有些产品可能在一天内需求量很高,但在接下来的五天内还没有买到。查询结果应该是“清洁剂”是每天购买的产品。

Date    |Product ID|Product|QTY|Price
02.01.19|HK2345|tablet     |2  |1500
02.01.19|HK2345|tablet     |2  |1500
02.01.19|FG4549|phone      |4  |800
02.01.19|DF2345|mouse      |5  |20
02.01.19|KO4865|monitor    |1  |450
02.01.19|SDF486|coffe mach |6  |300
02.01.19|POIJ48|cleaner    |1  |20
03.01.19|KO4GR |surround   |2  |450
03.01.19|SDFGEA|vaccum clea|8  |300
03.01.19|POIJ48|cleaner    |9  |20
04.01.19|HK2KOI|charger    |1  |15
04.01.19|HK2KOJ|cooler     |1  |20
04.01.19|HK2GDS|mouse pad  |10 |8
04.01.19|FG4549|phone      |4  |800
04.01.19|POIJ48|cleaner    |1  |20
04.01.19|POIJ48|cleaner    |9  |20
05.01.19|49894U|chair      |1  |100
05.01.19|49894U|chair      |5  |100
05.01.19|POIJ48|cleaner    |9  |20
05.01.19|JIPIO4|book       |1  |20
05.01.19|NGGO7|lamp        |1  |80
06.01.19|HK2KOI|charger    |1  |15
06.01.19|POIJ48|cleaner    |9  |20
07.01.19|48961G|keybord    |11 |20
07.01.19|POIJ48|cleaner    |9  |20
07.01.19|HRF584|headset    |2  |40
08.01.19|GRS4984|fiber cabl|10 |10
08.01.19|DGFE4 |magazine   |1  |5
08.01.19|POIJ48|cleaner    |9  |20
08.01.19|49894U|chair      |5  |100
08.01.19|DF2345|mouse      |5  |20
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |12 |1500
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |2  |1500
08.01.19|HK2345|tablet     |8  |1500
08.01.19|HK2345|tablet     |2  |1500

2 回复 | 直到 7 年前

Andomar 7 年前

Product count(distinct Date)

select  Procuct
,       count(distinct Date) as NumberOfDaysWithSales
,       sum(Qty) as TotalSold
,       sum(Qty*Price) as TotalRevenue
from    YourTable
group by
        Product
order by
        count(distinct Date) desc

Gordon Linoff 7 年前

select top (1) with ties product, count(distinct date) as num_days
from t
group by product
order by num_days desc;

with ties

select top (1) with ties product,
       count(distinct date) as num_days,
       sum(qty) as total_qty
from t
group by product
order by num_days desc, total_qty desc;