时间戳

首页 > 解决方案 > 在 SQL 中将数字拆分为范围

sql - 在 SQL 中将数字拆分为范围

问题描述

我正在尝试编写一个 SQL 查询来显示自 2015/01/31 以来客户资历的分布：

  1. 最多一个月
  2. 一到六个月之间
  3. 六个月到一年之间
  4. 一年多

我成功地对客户的月数进行了拆分和分组。

SELECT Seniority, COUNT(Customer_ID) [Number of Customers]
FROM
(SELECT Customer_ID,
DATEDIFF(MONTH, MIN(CONVERT(datetime, Order_Date)), '2015/01/31') Seniority
FROM Orders
GROUP BY Customer_ID) t
GROUP BY Seniority

在此处输入图像描述

如何按给定范围拆分？

预期的：

        Seniorty                |   Number of Customers
Up to one month                 |            0
Between one and six months      |            14
Between six months and one year |            1
Over a year                     |            0

标签： sqlsql-serverrange

解决方案

使用条件聚合：

WITH cte AS (
    SELECT
        'Up to one month' AS Seniority,
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') < 1 THEN 1 END) AS [Number of Customers],
        1 AS position
    FROM Orders
    UNION ALL
    SELECT
        'Between one and six months',
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') >= 1 AND
                        DATEDIFF(MONTH, Order_Date, '2015-01-31') < 6 THEN 1 END),
        2
    FROM Orders
    UNION ALL
    SELECT
        'Between six months and one year',
         COUNT( CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') >= 6 AND
                        DATEDIFF(MONTH, Order_Date, '2015-01-31') < 12 THEN 1 END),
         3
    FROM Orders
    UNION ALL
    SELECT
        'Over a year',
        COUNT(CASE WHEN DATEDIFF(MONTH, Order_Date, '2015-01-31') > 12 THEN 1 END),
        4
    FROM Orders
)

SELECT
    Seniority,
    [Number of Customers]
FROM cte
ORDER BY
    position;

此答案假定该Order_Date列已经是日期或日期时间。如果不是，那么您应该做的第一件事就是将此列转换为实际的日期类型。

推荐阅读