日誌數據分析實戰：解決主賬號登錄時間分佈不連續的統計難題

日誌數據分析實戰：解決主賬號登錄時間分佈不連續的統計難題

在分析某公司一個月主賬號登錄原始日誌時，發現直接按小時/分鐘統計會導致時間軸不連續（無登錄時段缺失），圖表呈現斷裂，難以直觀發現異常空窗期。本文介紹通過「常量時間表 + 左連接」技巧，實現時間軸連續化統計的完整方案。

數據源結構

表 int_main_acct_login_100 字段包含：日誌 ID、主賬號、用戶名、操作時間 (PACKAGETIME, VARCHAR2)、客戶端/服務端 IP、應用系統 ID、操作名稱/類型、返回碼、日誌類型、組織機構、操作內容等。

痛點：原始統計 SQL 產生斷點

SELECT TO_CHAR(TO_DATE(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24'), COUNT(t.operatetype)
FROM int_main_acct_login_100 t
GROUP BY TO_CHAR(TO_DATE(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
ORDER BY 1;

結果僅包含有登錄記錄的小時，如 00:00-08:00 有數據，但 09:00 後無記錄則完全缺失，無法在圖表中體現「這段時間無人登錄」的業務事實。

解決方案：構建常量時間維度表

1. 創建時間基準表

CREATE TABLE yk_tb_time (mtime TIMESTAMP, pid NUMBER);

2. 存儲過程填充 31 天 × 24 小時 = 744 條記錄

CREATE OR REPLACE PROCEDURE pro_yk_tb_time IS
  i NUMBER := 0;
BEGIN
  WHILE i < 744 LOOP
    INSERT INTO yk_tb_time VALUES (
      TO_DATE('2016-5-1 0:0:1','yyyy-mm-dd hh24:mi:ss') + i/24,
      yk_seq.NEXTVAL
    );
    i := i + 1;
  END LOOP;
  COMMIT;
END;

關鍵點：Oracle 日期運算中 +1/24 代表加 1 小時（1天=24小時），TO_DATE 必須寫全格式至秒，再用 TO_CHAR 截取所需粒度。

3. 左連接實現連續統計

SELECT TO_CHAR(s.mtime,'yyyy-mm-dd'), COUNT(t.packagetime)
FROM yk_tb_time s
LEFT JOIN int_main_acct_login_100 t
  ON TO_CHAR(TO_DATE(t.packagetime,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') = TO_CHAR(s.mtime,'yyyy-mm-dd')
GROUP BY TO_CHAR(s.mtime,'yyyy-mm-dd')
ORDER BY 1;

注意：常量表 yk_tb_time 必須置於左側，否則右側無匹配行會導致該日期丟失（如 5 月 13 日零登錄仍能顯示 count=0）。

成果展示

圖表清晰揭示 5 月 13 日零登錄異常，及特定時段登錄頻次突變，為後續設定閾值告警提供直觀依據。

避坑指南

• VARCHAR 時間字段轉 DATE 必須 TO_DATE(..., 'yyyy-mm-dd hh24:mi:ss') 寫全，否則截取小時會報錯或結果錯誤。
• 常量表粒度（小時/天/分鐘）需與業務分析粒度一致，過細導致表膨脹，過粗掩蓋異常。
• 左連接時 ON 條件雙邊格式必須完全一致（同樣用 TO_CHAR(TO_DATE(...), 'fmt')）。