直方图:在SQL中使用变量桶计数顺序

Question

我有一张列有订单、物品和价格的桌子。我正试图根据价格为每个项目生成柱状图。

Create Table #Customer_Pricing
(
customer_id int,
item_id VARCHAR(10),
qty DECIMAL(5,2),
price DECIMAL(5,2),
)
;
GO

-- Insert Statements
Insert into #Customer_Pricing values(128456, 'SOM 555', 8, 2.50)
Insert into #Customer_Pricing values(123856, 'SOM 554', 1, 2.50)
Insert into #Customer_Pricing values(123456, 'SOM 554', 55, 2.00)
Insert into #Customer_Pricing values(123556, 'SOM 555', 2, 2.20)
Insert into #Customer_Pricing values(123456, 'SOM 553', 12, 2.13)
;

对于每个项目，我想要3个垃圾箱，所以我决定了垃圾箱的大小，方法是将最大值的差值除以3，然后把这个值加到最小。

WITH Stats_Table_CTE (item_id2,max_p, min_p, int_p, r1_upper, r2_lower, r2_upper, r3_lower)
AS
(   SELECT  item_id
            ,max(price) 
            ,min(price)
            ,(max(price) - min(price))/3
            ,min(price)+(max(price) - min(price))/3-0.01
            ,min(price)+(max(price) - min(price))/3         
            ,min(price)+((max(price) - min(price))/3)*2-0.01
            ,min(price)+((max(price) - min(price))/3)*2                                             
        FROM #Customer_Pricing
        GROUP BY item_id)

现在，我需要计算每个范围和每个项目的频率。我试图用SUM(CASE.)来做。但没有成功。

SELECT item_id
    ,SUM(CASE WHEN price <= r1_upper, THEN 1 ELSE 0 END) AS r1_count
    ,SUM(CASE WHEN price >= r2_lower AND <= r2_upper, THEN 1 ELSE 0 END) AS r2_count
    ,SUM(CASE WHEN price >= r3_lower, THEN 1 ELSE 0 END) AS r3_count
FROM Stats_Table_CTE
GROUP BY item_id

我还试图在表单中使用item_id，价格计数(价格<= r1_upper)作为r1_count.但我被困住了

在一次尝试中，内部JOINed -- #Customer_Pricing表和Stats_Table_CTE --但不知道从那里到哪里。

理想情况下，我希望输出表显示如下：*这不是实际数据，但我将其包括进来以显示所需的输出格式。项目ID min_p r1_upper (r2 bins) r3_lower max_p r1_count r2_ct SOM 553 2.00 2.16节省空间2.33 2.50 2 1 SOM 554 2.13 2.88 3.25 1 0 SOM 555 2.31 2.512.72 2.92 3 2

*输出表的格式为off，但我有项ID、回收箱和顶部按项目分组的计数

Answer 1

以下是我的建议：

WITH Stats_Table_CTE AS (
    SELECT  item_id, max(price) as maxprice, min(price) as minprice,
            (max(price) - min(price))/3 as binsize
    FROM #Customer_Pricing
    GROUP BY item_id
   )
SELECT cp.item_id,
     SUM(CASE WHEN price < minprice + binsize THEN 1 ELSE 0
         END) AS r1_count
     SUM(CASE WHEN price >= minprice + binsize AND price < minprice+ 2*binsize
              THEN 1 ELSE 0
         END) AS r2_count
     SUM(CASE WHEN price >= minprice + 2*binsize
              THEN 1 ELSE 0
         END) AS r3_count
FROM #Customer_Pricing cp JOIN
     Stats_Table_CTE st
     ON st.item_id = cp.item_id
GROUP BY cp.item_id

重要的部分是连接回#Customer_Pricing。同样重要的是简化逻辑--您可以为回收箱定义边界并使用<，而不是为每个桶定义下限和上限。另外，您的查询中有一些语法错误。

注意，在许多数据库中，CTE是不必要的，因为您可以只使用窗口函数。您的问题没有被数据库标记(虽然我可以猜到它是什么)，所以这种更改似乎是没有道理的。