我已经设法将客户最新的营销偏好收集到以下表格中,但无法弄清楚如何将这些结果压缩到每[CustomerKey]
一行中。下面是一个示例,显然,当我在下面进一步运行查询时,我们有数十万个实际结果。
CustomerKey SMS Phone Email Post Group
--------------------------------------------------------------
ClarkeM21-9EF1976-08-03 No
ClarkeM21-9EF1976-08-03 No
ClarkeM21-9EF1976-08-03 No
ClarkeM21-9EF1976-08-03 No
ClarkeM21-9EF1976-08-03 No
SmithNG12-8AS1980-02-03 No
SmithNG12-8AS1980-02-03 No
SmithNG12-8AS1980-02-03 Yes
SmithNG12-8AS1980-02-03 No
SmithNG12-8AS1980-02-03 Yes
这是我用来创建上述内容的相当重复的查询,以防万一有人看到它并认为我从完全错误的角度来解决这个问题;
IF OBJECT_ID('tempdb..#ClientRefPreferenceDates') IS NOT NULL
DROP TABLE #ClientRefPreferenceDates;
IF OBJECT_ID('tempdb..#MaxDatePerClientRef') IS NOT NULL
DROP TABLE #MaxDatePerClientRef;
IF OBJECT_ID('tempdb..#LatestPrefsbyCustKey') IS NOT NULL
DROP TABLE #LatestPrefsbyCustKey;
SELECT
mcp.[CustomerKey],
mcp.[SMS],
MAX(mcp.[SMS_DateTime]) AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
INTO
#ClientRefPreferenceDates
FROM
[audit].[Marketing_Consent_Prefs] mcp
WHERE
mcp.[EndDate] IS NULL
GROUP BY
CustomerKey, SMS
UNION ALL
SELECT
mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
mcp.[Phone],
Max(mcp.[Phone_DateTime]) AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM
[audit].[Marketing_Consent_Prefs] mcp
WHERE
mcp.[EndDate] IS NULL
GROUP BY
CustomerKey, Phone
UNION ALL
SELECT
mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
mcp.Email,
MAX(mcp.[Email_DateTime]) AS EmailDate,
'' AS Post,
'' AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM
[audit].[Marketing_Consent_Prefs] mcp
WHERE
mcp.[EndDate] IS NULL
GROUP BY CustomerKey, Email
UNION ALL
SELECT mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
mcp.Post,
Max(mcp.[Post_DateTime]) AS PostDate,
'' AS [Group],
'' AS GroupDate
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY CustomerKey, Post
UNION ALL
SELECT mcp.[CustomerKey],
'' AS SMS,
'' AS SMSDate,
'' AS Phone,
'' AS PhoneDate,
'' AS Email,
'' AS EmailDate,
'' AS Post,
'' AS PostDate,
mcp.[Group],
Max(mcp.[Group_DateTime]) AS GroupDate
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY CustomerKey, [Group]
SELECT CustomerKey,
SMS,
Phone,
Email,
Post,
[Group]
FROM #ClientRefPreferenceDates
GROUP BY CustomerKey, SMS, Phone, Email, Post, [Group]
ORDER BY CustomerKey
最简单的方法是将查询包装在另一个GROUP BY
SELECT CustomerKey, MAX(SMS), MAX(Phone), MAX(Email), MAX(Post), MAX(Group)
FROM (
your query
) a
GROUP BY CustomerKey
窗口函数应该这样做
SELECT distinct
mcp.[CustomerKey],
mcp.[SMS],
Max(mcp.[SMS_DateTime]) over (partition by CustomerKey, SMS) AS SMSDate,
Max(mcp.[Phone_DateTime]) over (partition by CustomerKey, Email) AS PhoneDate
INTO #ClientRefPreferenceDates
FROM [audit].[Marketing_Consent_Prefs] mcp
WHERE mcp.[EndDate] IS NULL
GROUP BY