我有一个表的销售项目，其中有如下列，如
SoldItemIDSoldID SoldAmountDateOfPurchaseDateOfActivation CreatedDate
SoldItemID
SoldID
SoldAmount
DateOfPurchase
DateOfActivation
CreatedDate
如何在sql中获得每周平均售出的商品数量？任何帮助都将不胜感激！
gkl3eglg1#
我知道我参加聚会迟到了，但万一有人在找我，这也许行得通：
select date_part('week', transaction_date) week_of_the_year, item_id, avg(sold_qty) from your_table group by 1, 2 order by 1
select date_part('week', transaction_date) week_of_the_year, item_id,
avg(sold_qty) from your_table group by 1, 2 order by 1
cyej8jka2#
对于sql server
WITH CTE AS( SELECT * FROM ( VALUES ('2018-08-23', 'A', 10), ('2018-08-23', 'B', 10), ('2018-08-24', 'A', 14), ('2018-08-31', 'A', 8), ('2018-08-31', 'B', 10) ) as list (Date_, Item, Amount))select WKnum, MIN(Date_), MAX(Date_), Item, AVG(Amount) from( select *, DATEPART(WK, Date_) WKnum from CTE ) as Agroup by WKnum, Item
WITH CTE AS(
SELECT
*
FROM (
VALUES
('2018-08-23', 'A', 10),
('2018-08-23', 'B', 10),
('2018-08-24', 'A', 14),
('2018-08-31', 'A', 8),
('2018-08-31', 'B', 10)
) as list (Date_, Item, Amount)
)
select
WKnum, MIN(Date_), MAX(Date_), Item, AVG(Amount)
from(
*,
DATEPART(WK, Date_) WKnum
from CTE
) as A
group by WKnum, Item
kpbwa7wx3#
尝试下面的查询：它将在sql server上工作
select datepart(week,DateOfPurchase),avg(item)from(select DateOfPurchase,count(SoldItemID) itemfrom tablename group by DateOfPurchase) agroup by datepart(week,DateOfPurchase)
select datepart(week,DateOfPurchase),avg(item)
from
(
select DateOfPurchase,count(SoldItemID) item
from tablename group by DateOfPurchase) a
group by datepart(week,DateOfPurchase)
3条答案
按热度按时间gkl3eglg1#
我知道我参加聚会迟到了，但万一有人在找我，这也许行得通：
cyej8jka2#
对于sql server
kpbwa7wx3#
尝试下面的查询：它将在sql server上工作