草庐IT

sql 连续活跃天数

三三呀 2023-04-27 原文

1. 背景

已知数据集为：

目的：
计算每个uid的连续活跃天数，并且每一段活跃期内的开始时间和结束时间

2. 步骤

第一步：处理数据集
处理数据集，使其满足每个uid每个日期只有一条数据。
第二步：以uid为主键，按照日期进行排序，计算row_number.

SELECT  uid
          ,`征信查询日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
FROM   input

两个关键点：

  1. 序号rn可以看做一直活跃的情况下，活跃日期最大值和活跃日期最小值之间的天数差。那么，日期最大值与日期最小值之差如果不等于序号，就表明中间有不连续。
  2. 用'征信查询日期' - rn 可以计算一列"关键列",连续时间段内，它的关键列值是一样的
select *,DATE_SUB(`征信查询日期`,`rn`) as `关键列` from (
SELECT  uid
          ,`征信查询日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
FROM   input)


第三步：以uid和关键列作为主键。

select uid, `关键列`,count(*) as `连续活跃天数`, min(`征信查询日期`) as `活跃开始时间`, max(`征信查询日期`) as `活跃结束时间` from (
select *, DATE_SUB(`征信查询日期`,`rn`) as `关键列` from (
SELECT  uid
          ,`征信查询日期`
          ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
          ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
FROM   input
  ) )group by uid, `关键列`

sql连续uidPARTITION2511413MySQL

有关sql 连续活跃天数的更多相关文章

  1. ruby - 如何以所有可能的方式将字符串拆分为长度最多为 3 的连续子字符串？ - 2

    我试图获取一个长度在1到10之间的字符串，并输出将字符串分解为大小为1、2或3的连续子字符串的所有可能方式。例如:输入:123456将整数分割成单个字符，然后继续查找组合。该代码将返回以下所有数组。[1,2,3,4,5,6][12,3,4,5,6][1,23,4,5,6][1,2,34,5,6][1,2,3,45,6][1,2,3,4,56][12,34,5,6][12,3,45,6][12,3,4,56][1,23,45,6][1,2,34,56][1,23,4,56][12,34,56][123,4,5,6][1,234,5,6][1,2,345,6][1,2,3,456][123

  2. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  3. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  4. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  5. ruby - 如何用 Nokogiri 解析连续的标签？ - 2

    我有这样的HTML代码:Label1Value1Label2Value2...我的代码不起作用。doc.css("first").eachdo|item|label=item.css("dt")value=item.css("dd")end显示所有首先标记，然后标记标签，我需要“标签:值” 最佳答案 首先，您的HTML应该有和中的元素:Label1Value1Label2Value2...但这不会改变您解析它的方式。你想找到s并遍历它们，然后在每个你可以使用next_element得到;像这样:doc=Nokogiri::HTML(

  6. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  7. ruby - 我可以从 Ruby 中的系统调用中获得连续输出吗？ - 2

    当您在Ruby脚本中使用系统调用时，您可以像这样获得该命令的输出:output=`ls`putsoutput这就是thisquestion是关于。但是有没有办法显示系统调用的连续输出？例如，如果您运行此安全复制命令，以通过SSH从服务器获取文件:scpuser@someserver:remoteFile/some/local/folder/...它显示随着下载进度的连续输出。但是这个:output=`scpuser@someserver:remoteFile/some/local/folder/`putsoutput...不捕获该输出。如何从我的Ruby脚本中显示正在进行的下载进度？

  8. ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2

    我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。 最佳答案

  9. ruby - 获取数组中值的最大连续出现次数 - 2

    下面有没有更优雅的方法来实现这个:输入:array=[1,1,1,0,0,1,1,1,1,0]输出:4我的算法:streak=0max_streak=0arr.eachdo|n|ifn==1streak+=1elsemax_streak=streakifstreak>max_streakstreak=0endendputsmax_streak 最佳答案 类似于w0lf'sanswer,但通过从chunk返回nil来跳过元素:array.chunk{|x|x==1||nil}.map{|_,x|x.size}.max

  10. arrays - 检查连续数字 - 2

    我有一个整数数组m。我正在寻找一种方法来检查m的元素是否连续。有没有办法测试连续数字？我想出了这段代码，旨在在数组长度为四时工作:m.count==4&&(m.max-m.min)==3对于[1,1,1,4]或[0,0,0,3]错误地返回true。 最佳答案 Enumerable有一个非常方便的方法叫做each_cons是这样工作的:[1,2,3,4].each_cons(2).to_a#=>[[1,2],[2,3],[3,4]]也就是说，它会生成每组连续的n元素。在我们的例子中，n是2。当然，顾名思义，它返回一个Enumerato

随机推荐