代码之家 › 专栏 › 技术社区 › Jakub

生成按订单总数分组的订单统计信息

statistics sql-server-2005 sql-server sql

Jakub · 技术社区 · 15 年前

希望我能正确解释。我有一个行订单表(每个行订单由项目数量和价格组成,还有其他字段,但我遗漏了这些字段。)

“orderitems”表:

orderid | quantity | price
1       | 1        | 1.5000
1       | 2        | 3.22
2       | 1        | 9.99
3       | 4        | 0.44
3       | 2        | 15.99

所以为了得到订单总数,我会跑

SELECT     SUM(Quantity * price) AS total
FROM          OrderItems
GROUP BY OrderID

不过,我想得到所有低于1美元的订单总数的计数(只需提供一个计数)。

我的最终结果我希望能够定义范围: 低于1美元,1-3美元,3-5美元,5-10美元,10-15美元,15美元。等;

我的数据看起来是这样的(希望如此):

tunder1 | t1to3 | t3to5 | t5to10 | etc
10      | 500   | 123   | 5633   |

这样我就可以在我们的电子商务网站上展示客户订单的图表细目。现在我可以运行单独的SQL查询来获得这个结果,但是我想知道最有效的“单个SQL查询”是什么。我正在使用MS SQL Server。

目前,我可以运行类似这样的单个查询,以使总数低于1美元:

SELECT     COUNT(total) AS tunder1
FROM         (SELECT     SUM(Quantity * price) AS total
                       FROM          OrderItems
                       GROUP BY OrderID) AS a
WHERE     (total < 1)

我如何优化这个?事先谢谢!

4 回复 | 直到 15 年前

a'r 15 年前

select 
  count(case when total < 1 then 1 end) tunder1,
  count(case when total >= 1 and total < 3 then 1 end) t1to3,
  count(case when total >= 3 and total < 5 then 1 end) t3to5,
  ...
from
(
  select sum(quantity * price) as total
  from orderitems group by orderid
);

Mladen Prajdic 15 年前

你需要使用 HAVING 用于筛选分组值。

KM. 15 年前

试试这个:

DECLARE @YourTable table (OrderID int, Quantity int, Price decimal)
INSERT INTO @YourTable VALUES (1,1,1.5000)
INSERT INTO @YourTable VALUES (1,2,3.22)
INSERT INTO @YourTable VALUES (2,1,9.99)
INSERT INTO @YourTable VALUES (3,4,0.44)
INSERT INTO @YourTable VALUES (3,2,15.99)

SELECT
    SUM(CASE WHEN TotalCost<1 THEN 1 ELSE 0 END) AS tunder1
        ,SUM(CASE WHEN TotalCost>=1 AND TotalCost<3 THEN 1 ELSE 0 END) AS t1to3
        ,SUM(CASE WHEN TotalCost>=3 AND TotalCost<5 THEN 1 ELSE 0 END) AS t3to5
        ,SUM(CASE WHEN TotalCost>=5 THEN 1 ELSE 0 END) AS t5andup
    FROM (SELECT
              SUM(quantity * price) AS TotalCost
              FROM @YourTable
              GROUP BY OrderID
         ) dt

输出:

tunder1     t1to3       t3to5       t5andup
----------- ----------- ----------- -----------
0           0           0           3

(1 row(s) affected)

Quassnoi 15 年前

WITH    orders (orderid, quantity, price) AS
        (
        SELECT  1, 1, 1.5
        UNION ALL
        SELECT  1, 2, 3.22
        UNION ALL
        SELECT  2, 1, 9.99
        UNION ALL
        SELECT  3, 4, 0.44
        UNION ALL
        SELECT  4, 2, 15.99
        ),
        ranges (bound) AS
        (
        SELECT  1
        UNION ALL
        SELECT  3
        UNION ALL
        SELECT  5
        UNION ALL
        SELECT  10
        UNION ALL
        SELECT  15
        ),
        rr AS
        (
        SELECT  bound, ROW_NUMBER() OVER (ORDER BY bound) AS rn
        FROM    ranges
        ),
        r AS
        (
        SELECT  COALESCE(rf.rn, 0) AS rn, COALESCE(rf.bound, 0) AS f,
                rt.bound AS t
        FROM    rr rf
        FULL JOIN
                rr rt
        ON      rt.rn = rf.rn + 1
        )
SELECT  rn, f, t, COUNT(*) AS cnt
FROM    r
JOIN    (
        SELECT  SUM(quantity * price) AS total
        FROM    orders
        GROUP BY
                orderid
        ) o
ON      total >= f
        AND total < COALESCE(t, 10000000)
GROUP BY
        rn, t, f

输出:

rn      f       t       cnt
1       1       3       1
3       5       10      2
5       15      NULL    1

,也就是说 1 订单来源 $1 到 $3 ,2个订单来自 $5 到 $10 , 一 订购量超过 $15 .