SQL Server 如何 还 显示 与 SQLserver 上 的 列 的 Q1 和 Q5 值 对应 的 空 值 ( 或 零 ) ?

dfty9e19  于 2022-11-21  发布在  其他
关注(0)|答案(3)|浏览(124)

我在SQL Server上做了以下练习:编写一个查询,为每个聚类列出属于该聚类的产品数量。该公司希望获得关于每个订单中每种产品的平均数量的销售分析,将它们分类为六个聚类:Q1(〈15)、Q2(15-20)、Q3(21-25)、Q4(26-30)、Q5(31-35)、Q6(〉35)。编写一个查询,列出每个产品的产品名称及其所属的群集。数据库为northwind
第一个
我还需要显示Q1和Q5的值。

qlvxas9a

qlvxas9a1#

您 可以 随时 植入 初始 计数 , 例如 :

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;

中 的 每 一 个
现在 , 所有 组 的 初始 计数 都 为 零 , 并 将 在 查询 中 找到 的 计数 添加 到 该 初始 计数 中 。
未经 测试 , 所以 请 让 我 知道 , 如果 你 发现 错误 . . .

j5fpnvbx

j5fpnvbx2#

您不需要临时表或表变量,可以使用虚拟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或主键进行分组。
请注意,上面的查询只获取实际销售的产品的结果。

tag5nh1u

tag5nh1u3#

通过将簇 * 和范围 * 存储在一个表中,可以使查询更加简单(这样就可以在使用相同细分的其他类似查询中重用它)。

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;

工作示例in this fiddle
同样,除非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;

相关问题