Hive | 求最大连续登录天数的hql

最新推荐文章于 2024-01-28 22:08:43 发布

csdn-延

最新推荐文章于 2024-01-28 22:08:43 发布

阅读量84

点赞数

分类专栏：数仓 hive 大数据文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/m0_37125796/article/details/131688664

版权

大数据同时被 3 个专栏收录

75 篇文章 8 订阅

订阅专栏

数仓

22 篇文章 0 订阅

订阅专栏

hive

20 篇文章 1 订阅

订阅专栏

SELECT
  user_id,
  MAX(count_val) AS max_count
FROM
  (
    SELECT
      user_id,
      symbol_date,
      count(*) AS count_val
    FROM(
        SELECT
          user_id,
          log_date,
          date_sub(log_date, cast(rn AS INT)) AS symbol_date
        from(
            SELECT
              user_id,
              log_date,
              ROW_NUMBER() OVER(
                PARTITION BY user_id
                ORDER BY
                  log_date
              ) AS rn
            FROM
              user_logging_format
          ) c
      ) d
    GROUP BY
      user_id,
      symbol_date
  ) e
GROUP BY
  user_id;