SQL Server -为每个可能的列组合选择完整的数据集

Question

这是一个我已经尝试了很长时间的问题。这很难，所以请原谅我的长文。

我有一个MS SQL Server数据库，其中有一个表与世界各地的名称相对应。还有另一个表，其中包含驻留在每个区域的数据中心。在另一个表中，列出了在每个数据中心中运行的环境(DEV、PROD等)。在另一个示例中，列出了在每个环境中运行的虚拟机“类型”。最后，在另一个表中，我列出了处于部署“批准”阶段的虚拟机列表。

vm“类型”的一些例子可以包括像“小”、“中”和“大”这样的标签。

我想创建一个select语句，它返回具有该关系系列中所有可能组合的行，而不管给定的虚拟机类型是否实际存在于数据中。

For even :从去年到明年的每个月，->显示每个环境、每个数据中心、每个区域中每个虚拟机“类型”的已批准/未批准VM的->计数->即使为零/空

正如这个答案中所建议的：SQL- pad results with extra rows我一直在使用像UNPIVOT和OUTER/CROSS APPLY这样的技巧，但是没有用，因为我不仅仅是想要得到一个二维的结果。在我的例子中，有五个。

表和数据(已简化)：

USE [infra]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[datacenter](
   [datacenter_id] [int] IDENTITY(1,1) NOT NULL,
   [region_id] [int] NULL,
   [datacenter] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[env](
   [env_id] [int] IDENTITY(1,1) NOT NULL,
   [env] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[region](
   [region_id] [int] IDENTITY(1,1) NOT NULL,
   [region] [nvarchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[vm_class](
   [vm_class_id] [int] IDENTITY(1,1) NOT NULL,
   [vm_class] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[virtual_machine](
   [virtual_machine_id] [int] IDENTITY(1,1) NOT NULL,
   [hostname] [nvarchar](255) NULL,
   [region] [nvarchar](255) NULL,
   [datacenter] [nvarchar](255) NULL,
   [env] [nvarchar](255) NULL,
   [approval_status] [nvarchar](255) NULL,
   [deployment_month] [date] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[datacenter] ON

GO
INSERT [dbo].[datacenter] ([datacenter_id], [region_id], [datacenter]) VALUES (1, 1, N'Datacenter A')
GO
INSERT [dbo].[datacenter] ([datacenter_id], [region_id], [datacenter]) VALUES (2, 2, N'Datacenter B')
GO
INSERT [dbo].[datacenter] ([datacenter_id], [region_id], [datacenter]) VALUES (3, 3, N'Datacenter C')
GO
INSERT [dbo].[datacenter] ([datacenter_id], [region_id], [datacenter]) VALUES (4, 4, N'Datacenter D')
GO
INSERT [dbo].[datacenter] ([datacenter_id], [region_id], [datacenter]) VALUES (5, 1, N'Datacenter E')
GO
SET IDENTITY_INSERT [dbo].[datacenter] OFF
GO
SET IDENTITY_INSERT [dbo].[env] ON

GO
INSERT [dbo].[env] ([env_id], [env]) VALUES (1, N'LAB')
GO
INSERT [dbo].[env] ([env_id], [env]) VALUES (2, N'DEV')
GO
INSERT [dbo].[env] ([env_id], [env]) VALUES (3, N'QA')
GO
INSERT [dbo].[env] ([env_id], [env]) VALUES (4, N'COB')
GO
INSERT [dbo].[env] ([env_id], [env]) VALUES (5, N'PROD')
GO
SET IDENTITY_INSERT [dbo].[env] OFF
GO
SET IDENTITY_INSERT [dbo].[region] ON

GO
INSERT [dbo].[region] ([region_id], [region]) VALUES (1, N'EUR')
GO
INSERT [dbo].[region] ([region_id], [region]) VALUES (2, N'APAC')
GO
INSERT [dbo].[region] ([region_id], [region]) VALUES (3, N'NAM')
GO
INSERT [dbo].[region] ([region_id], [region]) VALUES (4, N'LATAM')
GO
SET IDENTITY_INSERT [dbo].[region] OFF
GO
SET IDENTITY_INSERT [dbo].[vm_class] ON

GO
INSERT [dbo].[vm_class] ([vm_class_id], [vm_class]) VALUES (1, N'SMALL')
GO
INSERT [dbo].[vm_class] ([vm_class_id], [vm_class]) VALUES (2, N'MEDIUM')
GO
INSERT [dbo].[vm_class] ([vm_class_id], [vm_class]) VALUES (3, N'LARGE')
GO
INSERT [dbo].[vm_class] ([vm_class_id], [vm_class]) VALUES (4, N'ELASTIC')
GO
SET IDENTITY_INSERT [dbo].[vm_class] OFF
GO

下面是我到目前为止编写的SQL，它没有返回一个完整的dataset (在我期望为NULL的地方有一些“缺失”的行)。

SELECT
    all_dates.unpivoted_date,
    r.region,
    d.datacenter,
    e.env,
    v.vm_class,
    ISNULL(SUM(virtual_machine), 0) AS [vm count]
FROM vmachines vms
FULL OUTER JOIN region r ON r.region = vms.region
FULL OUTER JOIN datacenter d on d.datacenter = vms.dc_label_final
FULL OUTER JOIN env e on e.env = fcast.env
FULL OUTER JOIN vm_class v on v.vm_class = vms.vm_class
FULL OUTER JOIN 
(
    SELECT CONVERT( DATE, mapped_date ) AS unpivoted_date FROM
    (
        SELECT
            DATEADD( MONTH, -13, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_12,
            DATEADD( MONTH, -12, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_11,
            DATEADD( MONTH, -11, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_10,
            DATEADD( MONTH, -10, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_09,
            DATEADD( MONTH, -9, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_08,
            DATEADD( MONTH, -8, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_07,
            DATEADD( MONTH, -7, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_06,
            DATEADD( MONTH, -6, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_05,
            DATEADD( MONTH, -5, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_04,
            DATEADD( MONTH, -4, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_03,
            DATEADD( MONTH, -3, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_02,
            DATEADD( MONTH, -2, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_01,
            DATEADD( MONTH, -1, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_00,
            DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) m_01,
            DATEADD( MONTH, 1, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_02,
            DATEADD( MONTH, 2, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_03,
            DATEADD( MONTH, 3, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_04,
            DATEADD( MONTH, 4, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_05,
            DATEADD( MONTH, 5, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_06,
            DATEADD( MONTH, 6, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_07,
            DATEADD( MONTH, 7, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_08,
            DATEADD( MONTH, 8, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_09,
            DATEADD( MONTH, 9, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_10,
            DATEADD( MONTH, 10, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_11,
            DATEADD( MONTH, 11, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_12
    ) pvt
    UNPIVOT
    (
        mapped_date FOR date_item IN
        (
            mp_12,          mp_11,          mp_10,          mp_09,
            mp_08,          mp_07,          mp_06,          mp_05,
            mp_04,          mp_03,          mp_02,          mp_01,
            m_00,           m_01,           m_02,           m_03,
            m_04,           m_05,           m_06,           m_07,
            m_08,           m_09,           m_10,           m_11,
            m_12
        )
    ) AS wd
) all_dates ON all_dates.unpivoted_date = CONVERT( DATE, vms.approval_month )
WHERE unpivoted_date IS NOT NULL
GROUP BY
    all_dates.unpivoted_date,
    r.region,
    d.datacenter,
    e.env, 
    v.vm_class
ORDER BY
    all_dates.unpivoted_date,
    r.region,
    d.datacenter,
    e.env,
    v.vm_class

Answer 1

我认为问题在于，您正在根据您的approval_month日期标准连接vmachines表，这将从结果中删除特定区域/数据中心/类组合没有现有vmachines记录的所有记录。

尝试不带日期信息的简化版本的查询，以验证在不使用日期联接的情况下，是否返回完整记录。

SELECT
    r.region,
    d.datacenter,
    e.env,
    v.vm_class,
    ISNULL(SUM(virtual_machine), 0) AS [vm count]
FROM vmachines vms
FULL OUTER JOIN region r ON r.region = vms.region
FULL OUTER JOIN datacenter d on d.datacenter = vms.dc_label_final
FULL OUTER JOIN env e on e.env = fcast.env
FULL OUTER JOIN vm_class v on v.vm_class = vms.vm_class
GROUP BY r.region, d.datacenter, e.env, v.vm_class

作为解决方案，可以考虑创建一个用户函数，该函数将approval_month转换为所需的月份类别，以包含在SELECT和GROUP BY中。

select
    .....
    dbo.ApprovalMonth(vms.approval_month) as appmonth
GROUP BY .....,  appmonth

当审批日期值为NULL (没有匹配的区域/数据中心/类组合)时，该函数可以返回NULL，也可以返回您选择的字符串。

Answer 2

这是为我工作的SQL代码(交叉连接、取消透视和子查询的组合)：

SELECT xjoin.*,
COUNT(virtual_machine.*) AS [vm count]
-- and I also added a few more aggregate functions here --
FROM
(
    SELECT
        r.region,
        d.datacenter,
        e.env,
        v.vm_class,
        all_dates.unpivoted_date
    FROM region r
    CROSS JOIN datacenter d
    CROSS JOIN env e
    CROSS JOIN vm_class v
    CROSS JOIN
    (
        SELECT CONVERT( DATE, mapped_date ) AS unpivoted_date FROM
        (
            SELECT
                DATEADD( MONTH, -13, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_12,
                DATEADD( MONTH, -12, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_11,
                DATEADD( MONTH, -11, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_10,
                DATEADD( MONTH, -10, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_09,
                DATEADD( MONTH, -9, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_08,
                DATEADD( MONTH, -8, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_07,
                DATEADD( MONTH, -7, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_06,
                DATEADD( MONTH, -6, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_05,
                DATEADD( MONTH, -5, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_04,
                DATEADD( MONTH, -4, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_03,
                DATEADD( MONTH, -3, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_02,
                DATEADD( MONTH, -2, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) mp_01,
                DATEADD( MONTH, -1, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_00,
                DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) m_01,
                DATEADD( MONTH, 1, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_02,
                DATEADD( MONTH, 2, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_03,
                DATEADD( MONTH, 3, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_04,
                DATEADD( MONTH, 4, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_05,
                DATEADD( MONTH, 5, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_06,
                DATEADD( MONTH, 6, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_07,
                DATEADD( MONTH, 7, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_08,
                DATEADD( MONTH, 8, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_09,
                DATEADD( MONTH, 9, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_10,
                DATEADD( MONTH, 10, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_11,
                DATEADD( MONTH, 11, ( DATEADD( DAY, 1, EOMONTH( CURRENT_TIMESTAMP ) ) ) ) m_12
        ) pvt
        UNPIVOT
        (
            mapped_date FOR date_item IN
            (
                mp_12,          mp_11,          mp_10,          mp_09,
                mp_08,          mp_07,          mp_06,          mp_05,
                mp_04,          mp_03,          mp_02,          mp_01,
                m_00,           m_01,           m_02,           m_03,
                m_04,           m_05,           m_06,           m_07,
                m_08,           m_09,           m_10,           m_11,
                m_12
            )
        ) AS wd
    ) all_dates
) xjoin
FULL OUTER JOIN [virtual_machine] vm ON
    vm.deployment_month = xjoin.unpivoted_date
    AND vm.region = xjoin.region
    AND vm.datacenter = xjoin.datacenter
    AND vm.env = xjoin.env
    AND vm.vm_class = xjoin.vm_class
GROUP BY
    xjoin.region,
    xjoin.datacenter,
    xjoin.env,
    xjoin.vm_class,
    xjoin.unpivoted_date
ORDER BY
    region,
    datacenter,
    env,
    vm_class,
    unpivoted_date