一些牛逼的统计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);