sql-server - T-SQL 高效使用 PARTITION BY 和 DISTINCT

Question

我有下表来监视用户登录到应用程序：

CREATE TABLE [dbo].[userActivity](
    [userType] [nchar](10) NULL,
    [userInstanceID] [nchar](10) NULL,
    [userID] [nchar](10) NULL,
    [login] [datetime2](7) NULL
) ON [PRIMARY]
GO

我的数据的一个特殊性是唯一用户由 、 和 的userType组合userInstanceID确定userID。

例如，在下图中，我有三个不同的用户：

1. 客户 1 1（红色）
2. 员工 1 2（蓝色）
3. 客户 2 1（绿色）

我的目标是知道：

1. 每个用户登录的次数
2. 最近的登录
3. 最早登录

我有一些测试数据：

INSERT [dbo].[userActivity] ([userType], [userInstanceID], [userID], [login]) VALUES (N'customer  ', N'1         ', N'1         ', CAST(N'2020-09-17T18:00:07.2492412' AS DateTime2))
GO
INSERT [dbo].[userActivity] ([userType], [userInstanceID], [userID], [login]) VALUES (N'employee  ', N'1         ', N'2         ', CAST(N'2020-09-18T09:00:07.2494560' AS DateTime2))
GO
INSERT [dbo].[userActivity] ([userType], [userInstanceID], [userID], [login]) VALUES (N'customer  ', N'1         ', N'1         ', CAST(N'2020-08-17T03:00:07.2492412' AS DateTime2))
GO
INSERT [dbo].[userActivity] ([userType], [userInstanceID], [userID], [login]) VALUES (N'customer  ', N'2         ', N'1         ', CAST(N'2020-07-23T10:00:07.2492412' AS DateTime2))
GO
INSERT [dbo].[userActivity] ([userType], [userInstanceID], [userID], [login]) VALUES (N'customer  ', N'2         ', N'1         ', CAST(N'2020-10-25T11:00:07.2492412' AS DateTime2))
GO

我能够通过以下方式获得我需要的东西：

SELECT DISTINCT userType, userInstanceID, userID, numberOfLogins, MostRecentLogin, oldestLogin FROM (
    SELECT userType, userInstanceID, userID, 
        COUNT(login) OVER(PARTITION BY userType, userInstanceID, userID ORDER BY userType, userInstanceID, userID) AS numberOfLogins,
        max(login) OVER(PARTITION BY userType, userInstanceID, userID ORDER BY userType, userInstanceID, userID) AS MostRecentLogin,
        min(login) OVER(PARTITION BY userType, userInstanceID, userID ORDER BY userType, userInstanceID, userID) AS oldestLogin
        FROM dbo.userActivity) AS summary

我的问题是： 这是有效的方法吗？ 我为每个用户带来了数百万行和大约 20 列。

我很感激任何建议。

谢谢！

标签： sql-serverperformancetsqldistinctpartitioning

Answer 1

您所写的第一个“气味”是您的列在每种情况下都是 a) 相同和 b)列表¹PARTITION BY中唯一的非聚合列。SELECT

第二个“气味”是DISTINCT。不完全是。当有人说“好吧，我得到了我需要的结果，除了我只想要一个时我得到了多行”时，它经常被使用。糟糕的方法是应用DISTINCT而不考虑为什么你会得到这些多重结果。

在您的情况下，您会得到多个结果，因为您没有正确聚合。

回顾您的问题，您是在说“对于这些列的每个唯一组合，我想计算这些聚合”。这很好地定义了GROUP BY²。所以是的，编写此查询的直接方法是：

select userType, userInstanceID, userID,
       COUNT(*) as numLogins, MIN(login) as firstLogin, MAX(login) as lastLogin
from dbo.userActivity
group by userType, userInstanceID, userID

您会注意到它更短并且使用的功能更少，这通常是一种告诉您已将查询转换为最有可能被优化器优化的形式的方法。

---

¹总而言之，这意味着您可能会多次计算完全相同的结果行。您是否真的这样做取决于a）您是否有多个具有相同唯一组合的行以及b）优化器的智能程度。

²我强烈建议您在考虑的任何时候都DISTINCT应该思考GROUP BY。DISTINCT实际上是GROUP BY *³，但是在分组时很少没有聚合。

³除了*是“子句中的所有列”而不是“ / sSELECT产生的所有列”。FROMJOIN