如何在SQLserver上顯示與集群列的Q1和Q5值對應的空（或零）值？

我在 SQL Server 上進行了這個練習：撰寫一個查詢，為每個集群列出屬于其中的產品數量。公司希望獲得關于每個訂單中每種產品的平均數量的銷售分析，將它們分為六個集群：Q1 (<15)、Q??2 (15-20)、Q3 (21-25)、Q4 (26-30)、Q5 (31-35)、Q6(>35)。撰寫一個查詢，列出每個產品的產品名稱和它所屬的集群。資料庫是北風

select count(ProductName) as prod_num ,cluster

from  (
        select  ProductName,
        case 
        when avg(Quantity) < 15 then 'Q1'
        when avg(Quantity) <= 20 then 'Q2'
        when avg(Quantity) between 21 and 25 then 'Q3'
        when avg(Quantity) between 26 and 30 then 'Q4'
        when avg(Quantity) between 31 and 35 then 'Q5'
        else 'Q6'
        end
        as cluster
        from [Order Details] od  join Products pr on od.ProductID=pr.ProductID
        group by  ProductName
        ) as clusters  
group by  cluster
order by cluster

OUTPUT
22  Q2
35  Q3
18  Q4
2   Q6

我還需要顯示 Q1 和 Q5 的值。

uj5u.com熱心網友回復：

您始終可以播種初始計數，例如：

declare @clusters table (prod_num int, cluster nchar(2));
insert into @clusters values
    (0, 'Q1'),(0, 'Q2'),(0, 'Q3'),(0, 'Q4'),(0, 'Q5'),(0, 'Q6');
    
select 
    t1.cluster,
    t1.prod_num   isnull(t2.prod_num, 0) as prod_num
from 
    @clusters t1
    left join
        (
            select count(ProductName) as prod_num ,cluster
            from  (
                    select  ProductName,
                    case 
                    when avg(Quantity) < 15 then 'Q1'
                    when avg(Quantity) between 15 and 20 then 'Q2'
                    when avg(Quantity) between 21 and 25 then 'Q3'
                    when avg(Quantity) between 26 and 30 then 'Q4'
                    when avg(Quantity) between 31 and 35 then 'Q5'
                    else 'Q6'
                    end
                    as cluster
                    from [Order Details] od  join Products pr on od.ProductID=pr.ProductID
                    group by  ProductName
                    ) as clusters  
            group by  cluster
        ) t2
    on t1.cluster = t2.cluster
order by t1.cluster;

現在我們所有組的初始計數為零，并將我們在查詢中找到的計數添加到該計數中。

未經測驗，如果您發現錯誤，請告訴我...

uj5u.com熱心網友回復：

您不需要臨時表或表變數，您可以使用虛擬VALUES子句來生成所有行。

您還可以通過將范圍編號也放入該表中來顯著簡化此操作。

select 
    t1.cluster,
    count(t2.AvgQuantity) as prod_num
from (VALUES
  ('Q1', -999999, 15),
  ('Q2', 15, 20),
  ('Q3', 20, 25),
  ('Q4', 25, 30),
  ('Q5', 30, 35),
  ('Q6', 35, 999999)
) t1(cluster, low, hi)
left join (
    select
      ProductName,
      avg(Quantity) as AvgQuantity
    from Products pr
    join [Order Details] od on od.ProductID = pr.ProductID
    group by
      pr.Id,
      pr.ProductName
) t2 on t2.AvgQuantity > t1.low AND t2.AvgQuantity <= t1.hi
group by
  t1.cluster
order by
  t1.cluster;

為了效率（以及可能的準確性），您還應該按產品 ID 或主鍵進行分組。

請注意，上面的查詢只為您獲取實際銷售的產品的結果。要包含所有產品，請將最內層更改join為left join

uj5u.com熱心網友回復：

您可以通過將集群和范圍存盤在表中來簡化查詢（以便您可以在使用相同細分的其他類似查詢中重復使用它）。我在這里使用#temp 表，但沒有理由這不能是靜態的、永久的維度表。

CREATE TABLE #clusters(cluster char(2), lo int, hi int,
  INDEX cix_cl CLUSTERED(lo,hi));

INSERT #clusters VALUES('Q1', 0,14),('Q2',15,20),('Q3',21,25),
                       ('Q4',26,30),('Q5',31,35),('Q6',36,2000000000);

SELECT prod_num = COUNT(p.ProductName), cl.cluster
FROM #clusters AS cl
LEFT OUTER JOIN
(
  SELECT pr.ProductName, avgQ = AVG(od.Quantity) 
    FROM dbo.[Order Details] AS od
    INNER JOIN dbo.Products AS pr
    ON od.ProductID = pr.ProductID
    GROUP BY pr.ProductName
) AS p
ON p.avgQ BETWEEN cl.lo AND cl.hi
  GROUP BY cl.cluster;

這個小提琴中的作業示例。

再說一次，除非ProductName不是唯一的，并且您真的關心具有相同名稱的不同 ID 的平均值，而不僅僅是產品 ID，否則連接是不必要的，您可以進一步簡化：

SELECT prod_num = COUNT(p.ProductID), cl.cluster
FROM #clusters AS cl
LEFT OUTER JOIN
(
  SELECT ProductID, avgQ = AVG(Quantity) 
    FROM dbo.[Order Details]
    GROUP BY ProductID
) AS p
ON p.avgQ BETWEEN cl.lo AND cl.hi
  GROUP BY cl.cluster;

標籤：sqlsql服务器北风

上一篇：如何獲取每條記錄的最新日期？[復制]

下一篇：用于存盤權限的整數解碼位