SQL 查詢用戶瀏覽記錄：統計每用戶各標簽瀏覽次數

表結構

CREATE TABLE liulan (
    lljiluid INT PRIMARY KEY IDENTITY(1,1),
    userid INT NOT NULL,
    flagname VARCHAR(20) NOT NULL,
    liulantime DATETIME NOT NULL
);

核心查詢

SELECT 
    userid,
    flagname,
    COUNT(*) AS browse_count
FROM liulan
GROUP BY userid, flagname
ORDER BY userid, browse_count DESC;

結果示例

進階擴展

1. 獲取每用戶瀏覽最多的標簽 (Top 1)

;WITH Ranked AS (
    SELECT 
        userid,
        flagname,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY COUNT(*) DESC) AS rn
    FROM liulan
    GROUP BY userid, flagname
)
SELECT userid, flagname, cnt
FROM Ranked
WHERE rn = 1;

2. 最近 N 天熱門標簽統計

DECLARE @Days INT = 30;
SELECT 
    flagname,
    COUNT(*) AS total_views,
    COUNT(DISTINCT userid) AS unique_users
FROM liulan
WHERE liulantime >= DATEADD(DAY, -@Days, GETDATE())
GROUP BY flagname
ORDER BY total_views DESC;

3. 用戶瀏覽行為分析（會話化）

-- 假設 30 分鐘無操作視為新會話
;WITH Sessionized AS (
    SELECT 
        userid,
        flagname,
        liulantime,
        CASE 
            WHEN DATEDIFF(MINUTE, 
                LAG(liulantime) OVER (PARTITION BY userid ORDER BY liulantime), 
                liulantime) > 30 
            THEN 1 ELSE 0 
        END AS new_session_flag
    FROM liulan
),
SessionGroups AS (
    SELECT 
        userid,
        flagname,
        SUM(new_session_flag) OVER (PARTITION BY userid ORDER BY liulantime) AS session_id
    FROM Sessionized
)
SELECT userid, session_id, flagname, COUNT(*) AS pageviews
FROM SessionGroups
GROUP BY userid, session_id, flagname;

索引優化建議

-- 支持 GROUP BY userid, flagname
CREATE INDEX IX_liulan_userid_flagname ON liulan(userid, flagname);

-- 支持時間範圍查詢
CREATE INDEX IX_liulan_liulantime ON liulan(liulantime);

-- 覆蓋索引（若查詢僅需這三列）
CREATE INDEX IX_liulan_cover ON liulan(userid, flagname) INCLUDE (liulantime);

💡 核心原則：「GROUP BY 誰，就按誰分組統計」。需求「每用戶、每標簽、次數」直接對應 GROUP BY userid, flagname，切勿過度複雜化。