如何创建一系列的月来将稀疏数据加入其中? [英] How can I create a series of months to join sparse data to?

问题描述

我认为这是一个非常普遍的问题，但是我不知道该过程叫什么，因此我将通过一个示例进行描述.我的概念是我想将稀疏数据集加入一个完整的系列，例如一周中的几天，一年中的月份或任何有序集(例如，用于排名).稀疏数据中的空位置将在整个序列旁边显示为NULL.

I think this is a pretty common issue, but I don't know what the process is called, so I'll describe it with an example. The concept is that I want to join a sparse dataset to a complete series, such as the days of the week, months of the year, or any ordered set (for example, for ranking). Empty positions in the sparse data will show as NULL alongside the complete series.

假设我在SQL Server中运行以下查询以找出每月销售额.

Let's say I run the following query in SQL Server to find out monthly sales.

SELECT
    YEAR([timestamp]),
    MONTH([timestamp]),
    COUNT(*)
FROM table1
WHERE YEAR([timestamp]) = YEAR(GETDATE())
GROUP BY
    YEAR([timestamp]),
    MONTH([timestamp])
ORDER BY
    YEAR([timestamp]) DESC,
    MONTH([timestamp]) DESC;

但是，例如，如果销售仅在今年的5月和8月发生，那么返回结果将如下所示:

If, however, sales only occurred in May and August of this year, for example, then the return result would look like this:

2010    August    1234
2010    May       5678

我希望我的返回结果集如下所示:

I want my return result set to look like this:

2010    January
2010    February
2010    March
2010    April
2010    May        1234
2010    June
2010    July
2010    August     5678
2010    September
2010    October
2010    November
2010    December

我唯一知道的方法是:

SELECT
    YEAR(GETDATE()),
    month_index.month_name,
    sales_data.sales
FROM (
    SELECT 'January' as month_name, 1 as month_number
    UNION
    SELECT 'February', 2
    UNION
    SELECT 'March', 3
    UNION
    SELECT 'April', 4
    UNION
    SELECT 'May', 5
    UNION
    SELECT 'June', 6
    UNION
    SELECT 'July', 7
    UNION
    SELECT 'August', 8
    UNION
    SELECT 'September', 9
    UNION
    SELECT 'October', 10
    UNION
    SELECT 'November', 11
    UNION
    SELECT 'December', 12
) as month_index
LEFT JOIN (
    SELECT
        YEAR([timestamp]) AS year_name,
        MONTH([timestamp]) AS month_name,
        COUNT(*) AS sales
    FROM table1
    WHERE YEAR([timestamp]) = GETDATE()
    GROUP BY
        YEAR([timestamp]),
        MONTH([timestamp])
) AS sales_data
ON month_index.month_name = sales_data.month_name
ORDER BY
    month_index.month_number DESC;

是否有更好的方法来创建完整的日期和字母数字系列以将数据连接到其中?这个叫做是什么?

Is there a better way to create complete date and alphanumeric series to join data onto? And what is this called?

谢谢！

推荐答案

尝试如下操作:

DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETDATE()),0) )
      ,@EndDate=GETDATE()

;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateAdd(month,1,DateOf)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT * FROM AllDates

输出:

DateOf
-----------------------
2009-12-01 00:00:00.000
2010-01-01 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-01 00:00:00.000
2010-05-01 00:00:00.000
2010-06-01 00:00:00.000
2010-07-01 00:00:00.000

(8 row(s) affected)

这篇关于如何创建一系列的月来将稀疏数据加入其中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！