用户连续活跃区间记录表设计

最新推荐文章于 2024-10-10 10:27:31 发布
最新推荐文章于 2024-10-10 10:27:31 发布
阅读量537 收藏 1
点赞数
分类专栏： 自学笔记 文章标签： 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_59887059/article/details/123922395
版权
本文探讨了数据库设计中的活跃度字段处理，包括创建DWS.mall_app_uac_range表来存储用户活跃区间，并介绍了缓慢变化维的概念，提到了重写维度值和插入新维度行两种处理方式。此外，还提及了一种用32位二进制表示用户活跃天数的设计方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

字段包含

日期首访日期guid连续活跃区间起始日连续活跃期间结束日
2022-03-01g012022-03-012022-03-10
2022-03-01g012022-04-019999-12-31
2022-03-04g022022-03-049999-12-31
2022-04-01g032022-04-012022-04-03

建表

create table dws.mall_app_uac_range(
    guid            bigint
    ,first_login_dt string
    ,range_start_dt string
    ,range_end_dt   string
)
partitioned by (dt string)
stored as orc
tblproperties('orc.compress'=''snappy)

缓慢变化维概念 

提出：业务中根据

根据不同的业务需求，有些历史数据需要保存，有些则不需要

三种处理缓慢变化维的方式

1.重写纬度值

2.插入新的维度行

插人新的维度行。采用此种方式，保留历史数据，

维度值变化前的事实和过去的维度值关联，维度值变化后的事实和当前的维度值关联

还有另外一种活跃度设计

bitmap

以二进制的形式，总共有32位的二进制

第几天有用户活跃，第几个位上的数字就设置为1

AI大模型在用户画像构建中的作用
AI天才研究院
12-01 1575
《AI大模型在用户画像构建中的作用》 关键词：人工智能，用户画像，深度学习，大模型，隐私保护，商业应用 摘要：随着人工智能技术的飞速发展，AI大模型在用户画像构建中发挥了重要作用。本文将详细探讨AI大模型的发展背景、技术基础、用户画像构建方法及其应用案例，并分析AI大模型在用户画像构建中的挑战与未来展望。通过本文
SQL进阶技巧：如何设计一张1-180天注册、活跃留存表？
石榴姐yyds
02-04 2723
本文给出了一种1-180天注册活跃留存表计算模型，主要采用笛卡尔集的形式求解，这也是数据报表经常采用的方法，需要掌握。
测试用sql(用户连续活跃区间记录表)
weixin_45484707的博客
10-13 563
需求： 1.1、T-1日(10.11) 区间表 create table act_range( guid string, first_dt string, rng_start string, rng_end string ) partitioned by (dt string) row format delimited fields terminated by ',' ; 1.2、数据 vi rng.11 a,2020-10-01,2020-10-01,2020-10-03
大数据面试之离线数仓--用户连续活跃区间表的实现思路
tianxingcheng的博客
08-25 237
需要注意的是，为了提高查询效率，可以在用户连续活跃区间表中添加一些冗余字段，例如上一个连续活跃区间的结束时间，以便在查询时进行优化。同时，也需要定期更新该表，以反映最新的用户活跃情况。用户连续活跃区间表是离线数仓中的一种重要的分析表，它能够记录每个用户的连续活跃时间段，为业务分析提供有力的支持。3. 针对每个用户，遍历其所有的登录记录，根据一定的规则（例如，两次登录时间之差小于某个阈值）将其划分为不同的连续活跃段。5. 最后，将所有用户的连续活跃段汇总成一张表，并按照用户ID和活跃段起始时间进行排序存储。
app端-用户连续活跃区间记录表开发
JinVijay的博客
04-03 669
-- 用户连续活跃区间记录表 CREATE TABLE dws.mall_app_uac_range( guid bigint -- 用户id ,first_login_dt string -- 首访日期 ,range_start_dt string -- 区间起始日期 ,range_end_dt string -- 区间结束日期 ) partitioned by (dt string) stored as orc tblp.
连续活跃的用户
weixin_43852674的博客
05-28 388
题目1 现在有一张用户活跃全量表，列有id，活跃日期（全部），分区 如何求得最大连续活跃天数的ID way1 with t1 as (select id,row_number() over(partition by id order by lastactivetime asc) as ranks, lastactivetime from table) select id,ate_sub(lastactivetime,ranks) as difftime, min(lastactiveimte) as m
SQL练习——用户活跃区间记录表
weixin_43648241的博客
09-30 422
区间记录表：4号 g01,2020-09-01,2020-09-01,2020-09-01 g01,2020-09-01,2020-09-03,2020-09-03 g02,2020-09-01,2020-09-01,2020-09-02 g03,2020-09-01,2020-09-01,9999-12-31 g04,2020-09-02,2020-09-02,2020-09-02 g05,2020-09-02,2020-09-02,2020-09-03 g06,2020-09-02,2020
SQL连续登录、活跃、持仓天数问题
imalady的博客
10-10 963
面试常问问题之SQL连续问题@SQL连续**问题编辑于2024-10-10在我们面试过程当中，经常会被问到经典的SQL连续问题，可能是连续10天活跃用户数，可能是连续十天登录的用户，也可能是计算用户的持仓天数。下面我们综合chatGPT和力扣来进行一下SQL连续问题的一个汇总。
相关业务问题+系统问题+设计问题整理统计
热门推荐
张彦峰的博客
04-07 171万+
业务系统及其他相关面试问题整理：线上相关问题排查+高并发系统的限流+高并发秒杀系统设计+负载均衡+一个网站有 20 亿 url 存在一个黑名单中，这个黑名单要怎么存？若此时随便输入一个 url，你如何快速判断该 url 是否在这个黑名单中？并且需在给定内存空间（比如：500M）内快速判断出？
mysql如何查询两个日期之间最大的连续登录天数
09-09
在MySQL数据库中，查询两个日期之间用户最大的连续登录天数是一项常见的数据分析任务，尤其是在用户行为追踪和用户活跃度分析的场景下。这个问题的关键在于找出在指定日期范围内，用户连续登录的最长连续天数。通常...
从用户连续活跃的最大天数说起
分享数据科学家的自我修养
07-19 3259
问题引入 最近经常碰到这样的问题，每天每个城市播放最多的10首歌，某月每支股票连续下跌/上涨的最大天数，用户连续活跃的最大天数，初步看起来都和分析函数相关，考验逻辑思维和写复杂SQL的能力。 以用户连续活跃的最大天数为例 以Oracle的分析函数语法说明，首先模拟一些用户活跃的数据， -- 建表语句 DROP TABLE sigin; create table sigin( userid...
面试题: Hive-SQL查询连续活跃登录用户思路详解
chbxw
12-06 868
sql: 查询连续问题
Redis笔记 ——String （用位图统计一段时间内用户登录天数，与网站指定时间段内活跃用户数）
一纸春秋的博客
11-15 1872
redis是一个高性能的 key-value 数据库，是跨平台的非关系型数据库 redis基于内存，并且支持持久化。可以将内存中的数据保存在磁盘中，重启的时候可以再次加载进行使用。 redis支持的数据类型丰富，包括key-value类型数据，字符串（strings）， 散列（hashes）， 列表（lists）， 集合（sets）， 有序集合（sorted sets）等 redis的key值是二进制安全的，也就说可以用任何二进制序列作为key值，从简单字符串到一个图片文件都可以。 （1）key的取值不能.
数据分析-面试（SQL真题）
Robin_Pi的博客
03-10 1431
SQL 面试真题
网站活跃用户统计的思路与设计
weixin_30892987的博客
07-06 2300
简单的讲，活跃用户就是：一段时间内，有多少登录用户访问了你的站点。这儿的时间段可以为：天，周，月。同时也可以细分到各个子站点。通过活跃用户，我们可以看出一个站点的受欢迎程度，毕竟一个站点做的好不好，最重要的标准还是：有多少人在用，以及他们是否喜欢你的站点。要统计活跃用户，首先就要记录用户的访问明细，可以有2种方式：1 每天从IIS日志里，提取不重复的用户ID列表2 通过js脚本的方式，往统计站点提...
架构设计内容分享(六十二)：亿级用户日活统计，有几种方案？
之乎者也·的博客
12-24 1943
通俗易懂的讲，Big Key就是某个key对应的value很大，占用的redis空间很大，本质上是大value问题。key往往是程序可以自行设置的，value往往不受程序控制，因此可能导致value很大。redis中这些Big Key对应的value值很大，在序列化/反序列化过程中花费的时间很大，因此当我们操作Big Key时，通常比较耗时，这就可能导致redis发生阻塞，从而降低redis性能。
大数据企业级项目实战--Titan大型数据运营系统项目课程
06-01
课程简介：  本项目课程是一门极具综合性和完整性的大型项目课程；课程项目的业务背景源自各类互联网公司对海量用户浏览行为数据和业务数据分析的需求及企业数据管理、数据运营需求。 本课程项目涵盖数据采集与预处理、数据仓库体系建设、用户画像系统建设、数据治理（元数据管理、数据质量管理）、任务调度系统、数据服务层建设、OLAP即席分析系统建设等大量模块，力求原汁原味重现一个完备的企业级大型数据运营系统。  拒绝demo，拒绝宏观抽象，拒绝只讲不练，本课程高度揉和理论与实战，并兼顾各层次的学员，真正从0开始，循序渐进，每一个步骤每一个环节，都会带领学员从需求分析开始，到逻辑设计，最后落实到每一行代码，所有流程都采用企业级解决方案，并手把手带领学员一一实现，拒绝复制粘贴，拒绝demo化的实现。并且会穿插大量的原创图解，来帮助学员理解复杂逻辑，掌握关键流程，熟悉核心架构。   跟随项目课程，历经接近100+小时的时间，从需求分析开始，到数据埋点采集，到预处理程序代码编写，到数仓体系搭建......逐渐展开整个项目的宏大视图，构建起整个项目的摩天大厦。  由于本课程不光讲解项目的实现，还会在实现过程中反复揉和各种技术细节，各种设计思想，各种最佳实践思维，学完本项目并勤于实践的话，学员的收获将远远超越一个项目的具体实现，更能对大型数据系统开发产生深刻体悟，对很多技术的应用将感觉豁然开朗，并带来融会贯通能力的巨大飞跃。当然，最直接的收获是，学完本课程，你将很容易就拿到大数据数仓建设或用户画像建设等岗位的OFFER课程模块： 1. 数据采集：涉及到埋点日志flume采集系统，sqoop业务数据抽取系统等； 2. 数据预处理：涉及到各类字典数据构建，复杂结构数据清洗解析，数据集成，数据修正，以及多渠道数据的用户身份标识打通：ID-MAPPING等；3. 数据仓库：涉及到hive数仓基础设施搭建，数仓分层体系设计，数仓分析主题设计，多维分析实现，ETL任务脚本开发，ETL任务调度，数据生命周期管理等；4. 数据治理：涉及数据资产查询管理，数据质量监控管理，atlas元数据管理系统，atlas数据血缘管理等；5. 用户画像系统：涉及画像标签体系设计，标签体系层级关系设计，各类标签计算实现，兴趣类标签的衰减合并，模型标签的机器学习算法应用及特征提取、模型训练等；6. OLAP即席分析平台：涉及OLAP平台的整体架构设计，技术选型，底层存储实现，Presto查询引擎搭建，数据服务接口开发等；7. 数据服务：涉及数据服务的整体设计理念，架构搭建，各类数据访问需求的restapi开发等；课程所涉及的技术： 整个项目课程中，将涉及到一个大型数据系统中所用到的几乎所有主要技术，具体来说，包含但不限于如下技术组件：l Hadoopl Hivel HBasel SparkCore /SparkSQL/ Spark GRAPHX / Spark Mllibl Sqoopl Azkabanl Flumel lasal Kafkal Zookeeperl Solrl Prestop
用户画像系列第九篇：应用案例之表结构设计
王大卓的专栏
06-01 2307
本文摘自赵宏田老师的：“用户画像：方法论与工程化解决方案”。 表结构的设计也是画像开发过程中，需要解决的一个重要的问题。表结构设计的重点是要考虑：储存哪些信息、如何储存、如何应用这三个方面的问题。不同的业务背景有不同的设计方式，这里提供两个设计思路：一是每日全量数据的表结构；二是每日增量数据的表结构。 每日全量数据，即该表的日期分区中，记录这截止到当天的全量用户数据，日全量数据的优势是方便查询，缺点是不便与探查更细粒度的用户行为。每日增量数据，即该表日期分区中记录着当日的用户行为数据，日增量数据可视为OD.
sql求连续十天登录
最新发布
04-03
<think>好的，用户现在问的是如何在SQL中求连续十天登录的问题。这个问题其实挺常见的，特别是在处理用户行为数据的时候。用户可能需要找出那些连续十天都登录的用户，可能是为了做活动奖励或者分析用户活跃度。 首先，我需要回忆一下之前用户问过的关于窗口函数的问题，比如ROW_NUMBER、RANK和DENSE_RANK的区别。现在这个问题可能也会用到窗口函数，尤其是ROW_NUMBER，因为连续登录的问题通常涉及到日期的连续性判断。 用户的需求是找到连续登录十天的用户，可能的场景包括用户留存分析、活跃用户筛选或者奖励机制。但用户可能没有说明具体的数据结构，比如用户登录记录表的结构，比如是否有用户ID和登录日期字段，是否需要去重，因为同一个用户可能在同一天有多次登录记录。 接下来，我需要考虑解决方案。常见的解决连续问题的办法是使用窗口函数，通过将登录日期与行号结合，找到连续的日期。例如，给每个用户的登录日期排序，然后用登录日期减去行号的天数，如果连续的话，这个差值会相同。这样分组后统计每组的日期数量，就能找到连续的天数。 不过，这里需要注意去重，因为同一个用户可能在同一天登录多次，所以需要先对日期进行去重处理，使用DISTINCT或者GROUP BY来确保每个用户每天只有一条记录。 然后，构建步骤：首先获取每个用户每天的登录记录，按用户分组并按日期排序，然后使用ROW_NUMBER()生成序号，接着用登录日期减去序号的天数得到一个分组标识。如果日期是连续的，那么这个分组标识会是相同的。最后，统计每个分组内的天数是否大于等于10天。 可能还需要考虑日期的格式，比如是否是日期类型，是否需要转换，以及性能问题，如果数据量大的话，这样的查询可能会比较慢，需要适当的索引优化。 用户可能还会关心具体的SQL写法，所以需要给出示例代码，并解释每一步的作用。同时，可能需要提醒用户关于去重和日期处理的问题，比如如果表中存在时间戳，可能需要转换为日期格式。 另外，还要考虑是否有用户可能断断续续登录，但中间有中断，如何排除这些情况。比如，连续十天中间不能有一天缺失，所以分组后的日期数量必须至少有一次达到10天。 最后，总结一下步骤，确保用户能够理解并应用到实际的数据表中，同时提醒他们根据实际表结构调整字段名和条件。</think>在SQL中计算**连续N天登录**的问题（例如连续10天），核心思路是通过日期与排序的差值标识连续区间，以下是分步解决方案： --- ### 一、数据准备 假设有用户登录表 `login_records`，包含字段： - `user_id`（用户ID） - `login_date`（登录日期，需去重处理） **示例数据**： | user_id | login_date | |---------|------------| | 1 | 2023-01-01 | | 1 | 2023-01-02 | | 1 | 2023-01-03 | | ... | ... | --- ### 二、解决步骤 #### 1. **去重处理** 若存在同一天多次登录的情况，先对日期去重： ```sql WITH distinct_dates AS ( SELECT user_id, DATE(login_date) AS login_date -- 确保格式为日期类型 FROM login_records GROUP BY user_id, DATE(login_date) -- 去重 ) ``` #### 2. **生成连续标识** 使用窗口函数 `ROW_NUMBER()` 为每个用户的登录日期排序，再通过**日期 - 序号天数**生成分组标识： ```sql , date_groups AS ( SELECT user_id, login_date, -- 按用户分组，按日期排序 login_date - INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY AS group_flag -- 关键：连续日期差值相同 FROM distinct_dates ) ``` #### 3. **统计连续天数** 按用户和分组标识聚合，筛选连续天数≥10的记录： ```sql SELECT user_id, MIN(login_date) AS start_date, -- 连续区间起始日期 MAX(login_date) AS end_date, -- 连续区间结束日期 COUNT(*) AS consecutive_days -- 连续天数 FROM date_groups GROUP BY user_id, group_flag HAVING COUNT(*) >= 10; -- 筛选连续10天及以上 ``` --- ### 三、原理解析 1. **ROW_NUMBER()** 为每个用户的日期生成连续序号（如1,2,3...） 2. **login_date - 序号天数**： - 若日期连续，例如 `2023-01-01`（序号1）、`2023-01-02`（序号2），则差值为 `2023-01-01 - 1 day = 2022-12-31`，`2023-01-02 - 2 days = 2022-12-31`，两者`group_flag`相同 - 若日期中断，差值会变化，从而区分不同连续区间 --- ### 四、最终完整SQL ```sql WITH distinct_dates AS ( SELECT user_id, DATE(login_date) AS login_date FROM login_records GROUP BY user_id, DATE(login_date) ), date_groups AS ( SELECT user_id, login_date, login_date - INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY AS group_flag FROM distinct_dates ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM date_groups GROUP BY user_id, group_flag HAVING COUNT(*) >= 10; ``` --- ### 五、注意事项 1. **日期格式**：确保`login_date`为日期类型（非时间戳） 2. **跨月/跨年**：此方法自动处理跨月跨年的日期计算 3. **性能优化**：对`user_id`和`login_date`建立联合索引
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值