将数据分桶在SQL Server 2008中

Question

我要处理的数据长这样：

+----------+------------------+--------+
| specimen |       date       | bucket |
+----------+------------------+--------+
| 31598D   | 3/3/2010 11:38   |     10 |
| A113899  | 2/10/2010 13:50  |     11 |
| A121375  | 12/17/2010 10:06 |      2 |
| A122115  | 6/14/2010 9:33   |     10 |
| A122119  | 5/19/2010 10:08  |      3 |
| A122124  | 6/30/2010 11:43  |      4 |
| DD58834  | 6/17/2010 10:08  |      1 |
| 31598A   | 3/3/2010 11:36   |     10 |
+----------+------------------+--------+

我想知道是否有可能将其转换为类似于下图的频率分布：

我应该在SQL Server中使用pivot函数吗？如果是这样，怎么做呢？

请注意，我可以访问SSRS，并可以将其用作解决此问题的资源。

非常感谢您的指导和时间。

- Alex Gordon

---

@marc_s，你能帮忙看一下这个问题吗？http://stackoverflow.com/questions/10711220/outputting-full-sequential-range-of-values - Alex Gordon

Answer 1

WITH    bm (maxbucket) AS
        (
        SELECT  MAX(bucket)
        FROM    mydata
        ),
        buckets (bucket) AS
        (
        SELECT  1
        UNION ALL
        SELECT  bucket + 1
        FROM    buckets
        JOIN    bm
        ON      bucket < maxbucket
        )
SELECT  bucket, p.*
FROM    (
        SELECT  b.bucket, DATEPART(month, dt) AS mon
        FROM    buckets b
        LEFT JOIN
                mydata m
        ON      m.bucket = b.bucket
        ) q
PIVOT   (
        COUNT(mon)
        FOR
        mon IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
        ) p

- Quassnoi

---

非常感谢。我正在执行这条语句，需要一些时间。实际上，这是从一个视图中获取数据。视图上可以创建索引吗？ - Alex Gordon

@АртёмЦарионов：取决于视图定义：http://msdn.microsoft.com/library/ms191432.aspx - Quassnoi

很酷。这已经运行了15分钟，但仍然没有结果，你能建议我怎么做来加速它吗？ - Alex Gordon

或者您认为让SSRS处理数据透视表并仅将视图提供给SSRS会更好？ - Alex Gordon

@АртёмЦарионов：另外，您可以尝试将“buckets”材料化为表变量，递归CTE可能会产生奇特的计划。 - Quassnoi

Answer 2

select * from t
pivot (
        count (*) for datepart(m, date) 
        in ([01],[02],[03],[04],[05],
            [06],[07],[08],[09],[10],[11],[12])
) as CountSpec

试一下

- vittore

---

您将会缺少表中不存在的桶编号。 - Taryn

将其更改为count(bucket)。 - vittore

你能帮忙解决这个问题吗？http://stackoverflow.com/questions/10711220/outputting-full-sequential-range-of-values - Alex Gordon

Answer 3

这未经测试，因为我没有可用的SQL Server实例，但它应该说明问题；您可以在CASE语句上使用SUM()函数来生成所需的分布。

SELECT
      [bucket]
    , SUM( CASE WHEN DATEPART(MONTH,[date]) = 1 THEN 1 ELSE 0 END ) AS [Jan]
    -- repeat the above for each month
FROM
    [your_table]
GROUP BY
    [bucket]
ORDER BY
    [bucket] DESC

- Rory Hunter

---

你能帮忙解决这个问题吗？http://stackoverflow.com/questions/10711220/outputting-full-sequential-range-of-values - Alex Gordon