一些牛逼的统计SQL

SQL

 

1.查询连续2天,每天发帖大于等于2次的用户

SELECT
    USER_ID
FROM
    (
        SELECT
            USER_ID,
            DATEDIFF(CREATE_TIME, '1971-01-01') - rn AS diff,
            CREATE_TIME
        FROM
            (
                SELECT
                    USER_ID ,@r := @r + 1 AS rn,
                    CREATE_TIME
                FROM
                    (
                        SELECT
                            COMMENT_ID,
                            USER_ID,
                            CREATE_TIME,
                            count(1)
                        FROM
                            T_SD_COMMENT t,
                            (SELECT @r := 0) r
                        GROUP BY
                            USER_ID,
                            DATE(CREATE_TIME)
                        HAVING
                            count(1) > 1
                        ORDER BY
                            USER_ID,
                            CREATE_TIME
                    ) z
            ) z
    ) z
GROUP BY
    USER_ID ,diff
HAVING
    MAX(DATE(CREATE_TIME)) - MIN(DATE(CREATE_TIME)) > 0

 

 

2. 连续7天，每天都有发贴

SELECT DISTINCT USER_ID
FROM (SELECT MAX(date) - MIN(date) AS less, USER_ID
    FROM (SELECT date - rn AS diff, USER_ID  , date, rn
        FROM (SELECT @wy := @wy + 1 AS rn, USER_ID, datediff(CREATE_TIME, '1971-01-01') AS date, CREATE_TIME
            FROM (SELECT date(CREATE_TIME) AS CREATE_TIME, USER_ID
                FROM T_SD_COMMENT, (SELECT @wy := 0
                    ) w
                GROUP BY USER_ID, date(CREATE_TIME)
                ORDER BY USER_ID, date(CREATE_TIME)
                ) x
            ) x
        ) x
    GROUP BY diff, USER_ID
    ) x
WHERE less >= 6  

 

2. 每10钟为粒度的统计

GROUP BY
SUBSTR(createtime + '', 1, 11);