如何统计去过多商户的客户在各商户的数量？求最优SQL方案

原SQL说明 #

原SQL用于筛选**去过多个指定商户（valentinesDayMerchant定义的商户集合）**的客户，代码如下：

WITH valentinesDayMerchant AS (
SELECT m.MerchantId, m.MerchantGroupId, m.WebsiteName 
FROM Merchant m 
INNER JOIN OpeningHours oh ON m.MerchantId = oh.MerchantId AND oh.DayOfWeek = 'TUE'
LEFT JOIN devices.DeviceConnectionState AS dcs ON dcs.MerchantId = oh.MerchantId
WHERE MerchantStatus = '-' AND (m.PrinterType IN ('V','O') OR dcs.State = 1 OR dcs.StateTransitionDateTime > '2023-01-23') 
)

SELECT DISTINCT ul.UserLoginId, ul.FullName, ul.EmailAddress, ul.Mobile
FROM dbo.UserLogin AS ul
INNER JOIN dbo.Patron AS p ON p.UserLoginId = ul.UserLoginId
INNER JOIN valentinesDayMerchant AS m ON (m.MerchantId = ul.ReferringMerchantId OR m.MerchantId IN (SELECT pml.MerchantId FROM dbo.PatronMerchantLink AS pml WHERE pml.PatronId = p.PatronId AND ISNULL(pml.IsBanned, 0) = 0))
LEFT JOIN (
    SELECT mg.MerchantGroupId, mg.MerchantGroupName, groupHost.HostName [GroupHostName]
    FROM dbo.MerchantGroup AS mg
    INNER JOIN dbo.Merchant AS parent ON parent.MerchantId = mg.ParentMerchantId
    INNER JOIN dbo.HttpHostName AS groupHost ON groupHost.MerchantID = parent.MerchantId AND groupHost.Priority = 0
    ) mGroup ON mGroup.MerchantGroupId = m.MerchantGroupId 
LEFT JOIN (
    SELECT po.PatronId, MAX(po.OrderDateTime) [LastOrder]
    FROM dbo.PatronsOrder AS po
    GROUP BY po.PatronId
) orders ON orders.PatronId = p.PatronId
INNER JOIN dbo.HttpHostName AS hhn ON hhn.MerchantID = m.MerchantId AND hhn.Priority = 1
WHERE ul.UserLoginId NOT IN (1,2,100,372) AND ul.UserStatus <> 'D' AND (
    ISNULL(orders.LastOrder, '2000-01-01') > '2020-01-01' OR ul.RegistrationDate > '2022-01-01'
    )
GROUP BY ul.UserLoginId, ul.FullName, ul.EmailAddress, ul.Mobile
HAVING COUNT(m.MerchantId) > 1

最优实现方法 #

核心思路是先锁定符合条件的客户（去过多个商户的用户），再基于这个客户集合统计他们在各商户的到访数量，避免直接分组导致的筛选失效问题。

具体SQL实现 #

WITH valentinesDayMerchant AS (
SELECT m.MerchantId, m.MerchantGroupId, m.WebsiteName 
FROM Merchant m 
INNER JOIN OpeningHours oh ON m.MerchantId = oh.MerchantId AND oh.DayOfWeek = 'TUE'
LEFT JOIN devices.DeviceConnectionState AS dcs ON dcs.MerchantId = oh.MerchantId
WHERE MerchantStatus = '-' AND (m.PrinterType IN ('V','O') OR dcs.State = 1 OR dcs.StateTransitionDateTime > '2023-01-23') 
),
-- 第一步：获取所有符合条件的客户及其关联的商户，同时计算每个客户关联的商户总数
CustomerMerchantLinks AS (
SELECT 
    ul.UserLoginId,
    ul.FullName,
    m.MerchantId,
    m.WebsiteName,
    -- 窗口函数计算当前客户关联的商户总数
    COUNT(m.MerchantId) OVER (PARTITION BY ul.UserLoginId) AS TotalMerchantsVisited
FROM dbo.UserLogin AS ul
INNER JOIN dbo.Patron AS p ON p.UserLoginId = ul.UserLoginId
INNER JOIN valentinesDayMerchant AS m ON (m.MerchantId = ul.ReferringMerchantId OR m.MerchantId IN (SELECT pml.MerchantId FROM dbo.PatronMerchantLink AS pml WHERE pml.PatronId = p.PatronId AND ISNULL(pml.IsBanned, 0) = 0))
LEFT JOIN (
    SELECT po.PatronId, MAX(po.OrderDateTime) [LastOrder]
    FROM dbo.PatronsOrder AS po
    GROUP BY po.PatronId
) orders ON orders.PatronId = p.PatronId
INNER JOIN dbo.HttpHostName AS hhn ON hhn.MerchantID = m.MerchantId AND hhn.Priority = 1
WHERE ul.UserLoginId NOT IN (1,2,100,372) 
  AND ul.UserStatus <> 'D' 
  AND (ISNULL(orders.LastOrder, '2000-01-01') > '2020-01-01' OR ul.RegistrationDate > '2022-01-01')
)
-- 第二步：筛选出去过多个商户的客户，统计每个商户的到访客户数
SELECT 
    MerchantId,
    WebsiteName,
    COUNT(DISTINCT UserLoginId) AS CustomerVisitCount
FROM CustomerMerchantLinks
WHERE TotalMerchantsVisited > 1
GROUP BY MerchantId, WebsiteName
ORDER BY CustomerVisitCount DESC;

方法说明 #

1. 窗口函数替代GROUP BY+HAVING：用COUNT(m.MerchantId) OVER (PARTITION BY ul.UserLoginId)计算每个客户关联的商户总数，无需提前分组，保留了客户与商户的关联关系。
2. 两步筛选统计：先通过窗口函数标记出符合条件的客户（TotalMerchantsVisited > 1），再基于这些客户统计每个商户的到访数量，确保只统计目标客户群体。
3. 避免无效数据：全程基于原SQL的筛选条件，不会包含不符合要求的客户（如已注销、未满足活跃条件的用户）。

内容的提问来源于stack exchange，提问作者Justin Zhao