我正在使用adventureworks 2019，并希望使用销售收入数据进行预测。目前，数据被分类为salesOrders(因此SalesOrderID，只是在此查询中未被选中)。这就是为什么同一年的同一季度有许多行。
我正在使用当前查询:
SELECT
SST.[Name] AS RegionName,
CASE WHEN SSOH.[OnlineOrderFlag] = 1 THEN 'Online' ELSE 'Reseller' END AS [SalesChannel],
YEAR(SSOH.[OrderDate]) AS Year,
DATEPART(Quarter, SSOH.[OrderDate]) AS Quarter,
SSOD.[LineTotal] AS Revenue
FROM [Sales].[SalesOrderDetail] AS SSOD
LEFT JOIN [Sales].[SalesOrderHeader] AS SSOH
ON SSOD.[SalesOrderID] = SSOH.[SalesOrderID]
LEFT JOIN [Production].[Product] AS PP
ON SSOD.[ProductID] = PP.[ProductID]
LEFT JOIN [Sales].[SalesTerritory] AS SST
ON SST.TerritoryID = SSOH.TerritoryID
和输出:
RegionName SalesChannel Year Quarter Revenue
Southeast Reseller 2011 2 2024.994000
Southeast Reseller 2011 2 6074.982000
Southeast Reseller 2011 2 2024.994000
Southeast Reseller 2011 2 2039.994000
Southeast Reseller 2011 2 2039.994000
Southeast Reseller 2011 2 4079.988000
Southeast Reseller 2011 2 2039.994000
Southeast Reseller 2011 2 86.521200
Southeast Reseller 2011 2 28.840400
Southeast Reseller 2011 2 34.200000
Southeast Reseller 2011 2 10.373000
Southeast Reseller 2011 2 80.746000
Southeast Reseller 2011 2 419.458900
Southeast Reseller 2011 2 874.794000
Canada Reseller 2011 2 809.760000
Canada Reseller 2011 2 714.704300
Canada Reseller 2011 2 1429.408600
Canada Reseller 2011 2 20.746000
Canada Reseller 2011 2 115.361600
Canada Reseller 2011 2 1445.189800
Canada Reseller 2011 2 6074.982000
Canada Reseller 2011 2 4049.988000
Canada Reseller 2011 2 40.373000
Canada Reseller 2011 2 1637.400000
Canada Reseller 2011 2 8099.976000
Canada Reseller 2011 2 4079.988000
Canada Reseller 2011 2 57.680800
Canada Reseller 2011 2 4049.988000
Canada Reseller 2011 2 100.932500
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 2097.294500
Canada Reseller 2011 2 367.876400
Canada Reseller 2011 2 3499.176000
Canada Reseller 2011 2 551.814600
Canada Reseller 2011 2 2097.294500
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 838.917800
Canada Reseller 2011 2 2146.962000
Canada Reseller 2011 2 874.794000
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 356.898000
Canada Reseller 2011 2 5248.764000
Canada Reseller 2011 2 183.938200
Canada Reseller 2011 2 535.742400
Canada Reseller 2011 2 2146.962000
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 183.938200
Canada Reseller 2011 2 356.898000
Canada Reseller 2011 2 178.580800
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 874.794000
Southwest Reseller 2011 2 419.458900
Northwest Reseller 2011 2 2039.994000
Northwest Reseller 2011 2 8099.976000
Northwest Reseller 2011 2 28.840400
Northwest Reseller 2011 2 28.840400
Northwest Reseller 2011 2 4049.988000
Northwest Reseller 2011 2 6119.982000
Northwest Reseller 2011 2 2039.994000
Northwest Reseller 2011 2 2024.994000
Northwest Reseller 2011 2 40.373000
Northwest Reseller 2011 2 2039.994000
Northwest Reseller 2011 2 20.186500
Northwest Reseller 2011 2 57.680800
Northwest Reseller 2011 2 4049.988000
Northwest Reseller 2011 2 10.373000
Northwest Reseller 2011 2 4049.988000
Northwest Reseller 2011 2 2024.994000
Northwest Reseller 2011 2 34.200000
Northwest Reseller 2011 2 2024.994000
Southwest Reseller 2011 2 419.458900
Southwest Reseller 2011 2 2146.962000
Southwest Reseller 2011 2 356.898000
Southwest Reseller 2011 2 874.794000
Southwest Reseller 2011 2 838.917800
Southwest Reseller 2011 2 419.458900
Central Reseller 2011 2 17.100000
Central Reseller 2011 2 2039.994000
Central Reseller 2011 2 2024.994000
Central Reseller 2011 2 2024.994000
Canada Reseller 2011 2 2624.382000
Canada Reseller 2011 2 4293.924000
Canada Reseller 2011 2 2936.212300
Canada Reseller 2011 2 2516.753400
Canada Reseller 2011 2 173.042400
Canada Reseller 2011 2 1103.629200
Canada Reseller 2011 2 40.373000
Canada Reseller 2011 2 40.373000
Canada Reseller 2011 2 1749.588000
Canada Reseller 2011 2 20.746000
Canada Reseller 2011 2 367.876400
Canada Reseller 2011 2 2624.382000
Canada Reseller 2011 2 1258.376700
Canada Reseller 2011 2 838.917800
Canada Reseller 2011 2 551.814600
Canada Reseller 2011 2 838.917800
Canada Reseller 2011 2 838.917800
Canada Reseller 2011 2 367.876400
Canada Reseller 2011 2 28.840400
Canada Reseller 2011 2 838.917800
Canada Reseller 2011 2 1427.592000
Canada Reseller 2011 2 5248.764000
Canada Reseller 2011 2 1677.835600
Canada Reseller 2011 2 535.742400
Canada Reseller 2011 2 115.361600
Canada Reseller 2011 2 535.742400
Canada Reseller 2011 2 20.186500
Canada Reseller 2011 2 1070.694000
Canada Reseller 2011 2 1258.376700
Northwest Reseller 2011 2 714.704300
我试图将每年每个季度的所有收入相加，以获得(想象在收入栏中是2011年第1季度的一个值:
RegionName SalesChannel Year Quarter Revenue
Southeast Reseller 2011 1
Southeast Reseller 2011 2
Southeast Reseller 2011 3
Southeast Reseller 2011 4
Southeast Reseller 2012 1
Southeast Reseller 2012 2
Southeast Reseller 2012 3
我用过:
WHERE SST.[Name] = 'Australia' AND SSOH.[OnlineOrderFlag] = 1
GROUP BY DATEPART(Quarter, SSOH.[OrderDate]), SST.[Name], SSOH.[OnlineOrderFlag], SSOH.[OrderDate], SSOD.[LineTotal]
获取区域只有澳大利亚，销售渠道是"在线"，但是我得到这样的输出:
RegionName SalesChannel Year Quarter Revenue
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3399.990000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3399.990000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
Australia Online 2012 1 3578.270000
所以，它仍然没有像我希望的那样按季度分组收入。
有人知道解决这个问题的方法吗?
非常感谢。
谢谢!
我想你只是想要聚合:
SELECT SST.[Name] AS RegionName,
(CASE WHEN SSOH.[OnlineOrderFlag] = 1 THEN 'Online' ELSE 'Reseller'
END) AS [SalesChannel],
YEAR(SSOH.[OrderDate]) AS Year,
DATEPART(Quarter, SSOH.[OrderDate]) AS Quarter,
SUM(SSOD.[LineTotal]) AS Revenue
FROM [Sales].[SalesOrderDetail] SSOD LEFT JOIN
[Sales].[SalesOrderHeader] SSOH
ON SSOD.[SalesOrderID] = SSOH.[SalesOrderID] LEFT JOIN
[Production].[Product] AS PP
ON SSOD.[ProductID] = PP.[ProductID] LEFT JOIN
[Sales].[SalesTerritory] AS SST
ON SST.TerritoryID = SSOH.TerritoryID
GROUP BY SST.[Name] AS RegionName,
(CASE WHEN SSOH.[OnlineOrderFlag] = 1 THEN 'Online' ELSE 'Reseller'
END) AS [SalesChannel],
YEAR(SSOH.[OrderDate]) AS Year,
DATEPART(Quarter, SSOH.[OrderDate]);