在數(shù)據(jù)庫中統(tǒng)計(jì)連續(xù)相同數(shù)據(jù)是一個(gè)常見需求，例如統(tǒng)計(jì)用戶連續(xù)登錄天數(shù)或股票連續(xù)漲跌情況。核心思路是通過窗口函數(shù)為數(shù)據(jù)分配行號(hào)，然后計(jì)算當(dāng)前值與前一條記錄的差值，利用差值進(jìn)行分組統(tǒng)計(jì)。這種方法適用于大多數(shù)數(shù)據(jù)庫系統(tǒng)，如MySQL、PostgreSQL和SQL Server。

　　怎么讓數(shù)據(jù)庫統(tǒng)計(jì)連續(xù)相同數(shù)據(jù)?

　　在數(shù)據(jù)庫中統(tǒng)計(jì)連續(xù)相同數(shù)據(jù)是一個(gè)常見需求，例如統(tǒng)計(jì)用戶連續(xù)登錄天數(shù)、股票連續(xù)上漲/下跌天數(shù)等。以下是幾種主流數(shù)據(jù)庫中實(shí)現(xiàn)這一需求的方法：

　　一、通用思路(適用于大多數(shù)數(shù)據(jù)庫)

　　1. 使用窗口函數(shù)和差值法(通用方案)

　　核心邏輯：

　　通過窗口函數(shù)為每條記錄分配行號(hào)

　　計(jì)算當(dāng)前值與前一條記錄值的差值(或哈希值)

　　對(duì)差值進(jìn)行分組，統(tǒng)計(jì)每組的連續(xù)記錄數(shù)

　　示例SQL(以MySQL 8.0+為例)：

　　sqlWITH numbered_data AS (SELECT id, value,ROW_NUMBER() OVER (ORDER BY date_column) AS rnFROM your_table),grouped_data AS (SELECT id, value,rn,rn - ROW_NUMBER() OVER (PARTITION BY value ORDER BY date_column) AS grpFROM numbered_data)SELECT value,COUNT(*) AS consecutive_count,MIN(date_column) AS start_date,MAX(date_column) AS end_dateFROM grouped_dataGROUP BY value, grpORDER BY start_date;

　　二、不同數(shù)據(jù)庫的具體實(shí)現(xiàn)

　　1. MySQL/MariaDB(8.0+)

　　sql-- 統(tǒng)計(jì)連續(xù)登錄天數(shù)WITH login_data AS (SELECT user_id,login_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rnFROM user_logins),grouped_logins AS (SELECT user_id,login_date,rn - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grpFROM login_data)SELECT user_id,COUNT(*) AS consecutive_days,MIN(login_date) AS start_date,MAX(login_date) AS end_dateFROM grouped_loginsGROUP BY user_id, grpHAVING COUNT(*) >= 3 -- 至少連續(xù)3天ORDER BY user_id, start_date;

　　2. PostgreSQL

　　PostgreSQL支持更豐富的窗口函數(shù)，語法更簡潔：

　　sql-- 統(tǒng)計(jì)連續(xù)產(chǎn)品價(jià)格未變的天數(shù)WITH price_data AS (SELECT product_id,price_date,price,price - LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) AS price_change,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY price_date) AS rnFROM product_prices),grouped_prices AS (SELECT product_id,price_date,price,price_change,rn,CASE WHEN price_change IS NULL OR price_change != 0 THEN rnELSE LAG(rn, 1, rn) OVER (PARTITION BY product_id ORDER BY price_date)END AS grpFROM price_data)SELECT product_id,price,COUNT(*) AS consecutive_days,MIN(price_date) AS start_date,MAX(price_date) AS end_dateFROM grouped_pricesGROUP BY product_id, price, grpORDER BY product_id, start_date;

　　3. SQL Server

　　sql-- 統(tǒng)計(jì)連續(xù)銷售增長的產(chǎn)品WITH sales_data AS (SELECT product_id,sale_date,sales_amount,sales_amount - LAG(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS sales_change,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS rnFROM daily_sales),grouped_sales AS (SELECT product_id,sale_date,sales_amount,sales_change,rn,rn - ROW_NUMBER() OVER (PARTITION BY product_id, CASE WHEN sales_change IS NULL OR sales_change <= 0 THEN 0 ELSE 1 END ORDER BY sale_date) AS grpFROM sales_data)SELECT product_id,COUNT(*) AS consecutive_growth_days,MIN(sale_date) AS growth_start_date,MAX(sale_date) AS growth_end_dateFROM grouped_salesWHERE sales_change > 0GROUP BY product_id, grpORDER BY product_id, growth_start_date;

　　4. Oracle

　　sql-- 統(tǒng)計(jì)連續(xù)庫存不足的天數(shù)WITH inventory_data AS (SELECT product_id,inventory_date,inventory_level,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY inventory_date) AS rnFROM product_inventory),grouped_inventory AS (SELECT product_id,inventory_date,inventory_level,rn - ROW_NUMBER() OVER (PARTITION BY product_id, CASE WHEN inventory_level < 10 THEN 1 ELSE 0 END ORDER BY inventory_date) AS grpFROM inventory_data)SELECT product_id,COUNT(*) AS consecutive_shortage_days,MIN(inventory_date) AS shortage_start_date,MAX(inventory_date) AS shortage_end_dateFROM grouped_inventoryWHERE inventory_level < 10GROUP BY product_id, grpORDER BY product_id, shortage_start_date;

　　三、特殊場景處理

　　1. 處理時(shí)間間隔不連續(xù)的情況

　　如果數(shù)據(jù)不是連續(xù)日期(如周末或節(jié)假日無數(shù)據(jù))，需要先生成連續(xù)日期序列：

　　sql-- MySQL示例：生成日期序列并關(guān)聯(lián)實(shí)際數(shù)據(jù)WITH RECURSIVE date_series AS (SELECT MIN(date_column) AS date_val FROM your_tableUNION ALLSELECT DATE_ADD(date_val, INTERVAL 1 DAY)FROM date_seriesWHERE date_val < (SELECT MAX(date_column) FROM your_table)),full_data AS (SELECT d.date_val,t.valueFROM date_series dLEFT JOIN your_table t ON d.date_val = t.date_column)-- 然后使用前面的差值法統(tǒng)計(jì)連續(xù)相同值

　　2. 統(tǒng)計(jì)最長連續(xù)序列

　　sql-- 修改前面的查詢，添加排序獲取最長序列WITH numbered_data AS (SELECT id, value,ROW_NUMBER() OVER (ORDER BY date_column) AS rnFROM your_table),grouped_data AS (SELECT id, value,rn,rn - ROW_NUMBER() OVER (PARTITION BY value ORDER BY date_column) AS grpFROM numbered_data),consecutive_stats AS (SELECT value,COUNT(*) AS consecutive_count,MIN(date_column) AS start_date,MAX(date_column) AS end_date,RANK() OVER (PARTITION BY value ORDER BY COUNT(*) DESC) AS rnkFROM grouped_dataGROUP BY value, grp)SELECT value,consecutive_count,start_date,end_dateFROM consecutive_statsWHERE rnk = 1ORDER BY consecutive_count DESC;

　　四、性能優(yōu)化建議

　　添加適當(dāng)索引：確保排序和分組字段有索引

　　限制數(shù)據(jù)范圍：使用WHERE子句限制查詢時(shí)間范圍

　　物化中間結(jié)果：對(duì)于復(fù)雜查詢，考慮使用臨時(shí)表

　　分區(qū)表處理：對(duì)于超大數(shù)據(jù)集，考慮按時(shí)間分區(qū)

　　通過合理使用窗口函數(shù)和分組技術(shù)，數(shù)據(jù)庫可以高效統(tǒng)計(jì)連續(xù)相同數(shù)據(jù)。這種方法不僅適用于統(tǒng)計(jì)連續(xù)登錄或價(jià)格波動(dòng)，還可擴(kuò)展到各種需要識(shí)別連續(xù)模式的場景。掌握這一技術(shù)能幫助開發(fā)者更好地分析時(shí)間序列數(shù)據(jù)，提取有價(jià)值的業(yè)務(wù)洞察。