精华内容
下载资源
问答
  • sql判断连续三个月
    千次阅读
    2017-12-02 11:03:47

    我刚刚回答的"查询连续七天以上都有打卡的员工记录"有解决方案,给你转过来,希望能满足你的要求
    可以利用一下ORACLE的ROWNUM机制来做点文章,因为它是自增的
    我假设有这一张表 WORK
    ID,上班日期(DA)
    1,20080101
    1,20080102
    1,20080103
    2,20080101
    2,20080103
    3,20080101
    也可能是乱序
    第一步:排序
    SELECT * FROM WORK ORDER BY ID, DA
    第二步:日期-ROWNUM如果相同的话认为他们是连续的,然后GORUP BY一下
    SELECT A.*, TO_NUMBER (A.DA - ROWNUM) DAYS
    FROM (SELECT * FROM WORK ORDER BY ID, DA) A
    第三步:分别求出连续区间,让他们相减求出大于6的值
    SELECT C.ID
    (SELECT B.ID, (MAX (B.DA) - MIN (B.DA)) DAYS
    FROM (SELECT A.*, TO_NUMBER (A.DA - ROWNUM) DAYS
    FROM (SELECT * FROM WORK ORDER BY ID, DA) A) B
    GROUP BY B.ID, B.DAYS) C
    WHERE C.DAYS > 7

     

     

    原文链接:

    http://wenda.so.com/q/1382574132063427

     

    更多相关内容
  • sql求出连续三个以上金额为0的数据 结果为 2016 3 22010419580805261801 哈尔滨市南岗区许驰比萨店 0.000000 2016 4 22010419580805261801 哈尔滨市南岗区许驰比萨店 0.000000 2016 5 22010419580805261801 哈尔滨...
  • 题目:查询出连续3个月“本用水量”相同的用户,最新月份是9月,所以只显示9月的数据就行 1.首先我先用lag,lead函数根据用户名称进行分组,分别得到他们本月水量的上一次和下一次的结果 >2.然后我在去得到本月水量...

    在这里插入图片描述
    我在原题里面加强了一下
    在这里插入图片描述

    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1001', '202009', '张三', 22, 5);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1001', '202008', '张三', 19, 5);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1001', '202007', '张三', 14, 6);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1001', '202006', '张三', 10, 5);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1002', '202009', '李四', 421, 3);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1002', '202008', '李四', 321, 3);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1002', '202007', '李四', 221, 3);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1003', '202009', '王五', 151, 23);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1003', '202008', '王五', 141, 23);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1003', '202007', '王五', 121, 21);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1003', '202010', '王五', 111, 23);
    INSERT INTO test_nk.t (用户编号, 收费年月, 用户名称, 本月行度, 本月水量) VALUES ('1003', '202012', '王五', 211, 23);
    
    

    题目:查询出连续3个月“本用水量”相同的用户,最新月份是9月,所以只显示9月的数据就行
    1.首先我先用lag,lead函数根据用户名称进行分组,分别得到他们本月水量的上一次和下一次的结果

    在这里插入图片描述>2.然后我在去得到本月水量,a,b是否相等的个数 本月水量,a,b的个数如果大于等于1说明连续且相同
    在这里插入图片描述

    3.得到本月水量,a,b 的值,用之前得到的结果in进行过滤,得到连续三月的有哪些,得到过滤后的值,用row_number根据用户名称进行排序

    在这里插入图片描述

    4.最后对排序后的值过滤,得到最新的月份
    最后这个SQL还是有不足之处,没有考虑到连续的月份,有多个月份水量相同,不是连续的月份水量相同
    在这里插入图片描述

    select s.用户编号,s.收费年月,s.用户名称,s.本月行度,s.本月水量 from (
        select *, row_number() over (partition by b.用户名称 order by b.收费年月 desc) c
        from (
                 select *,
                        lag(a.本月水量, 1, a.本月水量) over (partition by a.用户名称)  a,
                        lead(a.本月水量, 1, a.本月水量) over (partition by a.用户名称) b
                 from t a
             ) b
        where (b.本月水量, b.a, b.b) in
              (
                  select a.本月水量,
                         a.a,
                         a.b
                  from (
                           select a.本月水量,
                                  a.a,
                                  a.b,
                                  count(case when a.本月水量 = a.a and a.a = a.b then 1 else null end) cnt
                           from (
                                    select *,
                                           lag(a.本月水量, 1) over (partition by a.用户名称)  a,
                                           lead(a.本月水量, 1) over (partition by a.用户名称) b
                                    from t a) a
                           group by a.本月水量, a.a, a.b
                           having cnt >= 1
                       ) a)
    ) s
    where s.c=1
    

    看了别人的思路之后,发现自己写的很烂,这个思路就是直接根据用户编号进行分组,得到他的下一次和下下一次的,如果第一次等于第二次,第二次等于第三次就说明是连续三次
    最后这个SQL还是有不足之处,没有考虑到连续的月份,有多个月份水量相同,不是连续的月份水量相同
    在这里插入图片描述

    select a.用户编号, 收费年月, 用户名称, 本月行度, 本月水量
    from (
             select *,
                    lead(a.本月水量, 1) over (partition by a.用户名称  order by 收费年月  desc) t1,
                    lead(a.本月水量, 2) over (partition by a.用户名称  order by 收费年月  desc) t2
             from t a
         ) a
    where a.本月水量=a.t1 and a.t1=a.t2
    

    /*1.先拿到收费的年月的最后二位,因为会有10 11 12 的存在,所以又要强转为signed,这样是为了保证十位是0的话就舍去,
    拿到月的后两位去跟当前的row_number排序做比较,这里比较巧妙的是按照月的最后一位与排序的序列做比较,判断是不是连续的越
    比如 01 03 04 05 07 排序的是 1 2 3 4 5 得到的结果是 0 1 1 1 2 这样就能够得到03 04 05是连续的月
    2.然后在根据用户编号和结果判断是否是连续的3个月
    3.然后在用lead函数拿到下个月和下下个月进行判断是否相等 得到相等的用户的最新月份
    (此SQL才是正确的写法)
    */
    在这里插入图片描述
    在这里插入图片描述

    在这里插入图片描述

    在这里插入图片描述
    在这里插入图片描述

    with result as (
        select *,
               cast(substr(cast(a.收费年月 as char), 5, 2) as signed) -
               row_number() over (partition by a.用户编号 order by a.收费年月) result
        from t a
    )
    select 用户编号, 收费年月, 用户名称, 本月行度, 本月水量,t1,t2
    from (
             select a.用户编号,
                    a.收费年月,
                    a.用户名称,
                    a.本月行度,
                    a.本月水量,
                    lead(a.本月水量, 1) over (partition by a.用户编号,a.result order by a.收费年月 desc) t1,
                    lead(a.本月水量, 2) over (partition by a.用户编号,a.result  order by a.收费年月 desc) t2
             from result a
             where a.用户编号 in (
                 select a.用户编号
                 from result a
                 group by a.用户编号, a.result
                 having count(1) >= 3
             )
         ) a
    where 本月水量=t1 and a.t1=a.t2
    

    1.首先根据用户编号和年月跟排序进行比较 来找到连续的年月
    2. 用dense_rank()进行排序(只要是相同的都会显示同一个数字)来找到同一用户编号下连续月份的相同的本月水量,计算出本月水量是否大于三个
    3.拿到得到的大于三个的连续的用户编号跟主表进行连接 排序得到最新的月份用户
    最后这个SQL还是有不足之处,没有考虑到连续的月份,有多个月份水量相同,不是连续的月份水量相同

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    select a.用户编号, a.收费年月, a.用户名称, a.本月行度,a.本月水量  from (
    select a.*,row_number() over (partition by a.用户编号 order by a.收费年月 desc) rn from t a
    inner join (
    select a.用户编号
    from (
    select *,dense_rank() over (partition by a.用户编号,a.continuous_month order by a.本月水量 ) as ressult from (
     select *,收费年月-dense_rank() over (partition by a.用户编号 order by a.收费年月) as continuous_month from t a ) a) a
    group by a.用户编号,a.ressult
    having count(1)>=3) b on a.用户编号=b.用户编号) a where a.rn=1
    
    展开全文
  • 写一条SQL统计连续三个月金额大于0及展现每个月的金额 需求: 下面是表FEE,字段是month(月份),service_id(电话号码),fee(出帐金额),请参看下面要求: Month service_id fee 201801 13012345678 50 201802 ...

    写一条SQL统计连续在三个月金额大于0及展现每个月的金额

    需求:

    下面是表FEE,字段是month(月份),service_id(电话号码),fee(出帐金额),请参看下面要求:
       Month      service_id       fee
       201801     13012345678     50
       201802     13012345678     60
       201803     13012345678     45
       201801     13012345677     70
       201802     13012345677     80
       201803     13012345676     60
       201801     13012345675     50
       201802     13012345675     40
       201803     13012345675     70
    请写出连续在201801,201802,201803三个月出帐金额大于0的电话号码,及201801出帐金额,201802出帐金额,201803出帐金额。
    (通过一个sql语句实现,可以通过case when 的复杂查询语句实现。)

    解答:

    数据库版本:oracle 11g

    --创建表和插入数据

    create table fee(month int,service_id bigint,fee int);
    insert into fee values(201801,13012345678,50);
    insert into fee values(201802,13012345678,60);
    insert into fee values(201803,13012345678,45);
    insert into fee values(201801,13012345677,70);
    insert into fee values(201802,13012345677,80);
    insert into fee values(201803,13012345676,60);
    insert into fee values(201801,13012345675,50);
    insert into fee values(201802,13012345675,40);
    insert into fee values(201803,13012345675,70);
    commit;

    --SQL语句
     

    select service_id, month
           , case when month = 201801 then sum(fee)
                  when month = 201802 then sum(fee)
                  when month = 201803 then sum(fee)
             end as fee
      from fee f
     where f.service_id in (--查询出三个月都出账的电话号码
                            select service_id
                              from fee
                             where month = 201801
                               and service_id in (select service_id from fee where month = 201802)
                               and service_id in (select service_id from fee where month = 201803)
                             )
     group by service_id, month
     order by 1;
    
    SERVICE_ID	MONTH	FEE
    13012345675	201801	50
    13012345675	201802	40
    13012345675	201803	70
    13012345678	201801	50
    13012345678	201802	60
    13012345678	201803	45
    

    注意:这里每个月有金额的话,默认是都大于0的,如果有0的话也简单,只需要再加一个大于0的条件;

     

    展开全文
  • SparkSQL统计——连续3或以上

    千次阅读 2021-12-23 16:26:24
    案例实现要求:统计连续3或以上都有购买记录的用户数 思路讲解: 1.以当前时间为基准值,获取到前面两的日期以及后面两的日期 通过lag和lead获取,注意它们的默认值,以及over中的.

     前言:本章用到了开窗函数,不了解的可以去看看。

    SQL开窗函数(窗口函数)详解_流水随清风的博客-CSDN博客_sql开窗函数

    数据源下载:

    链接: https://pan.baidu.com/s/1WMz8B-xJZjOoMmlaLurjZg

    提取码: eetv

    数据源展示:

    案例实现要求:统计连续3月或以上都有购买记录的用户个数

    思路讲解:

    1.以当前时间为基准值,获取到前面两月的日期以及后面两月的日期

    通过lag和lead获取,注意它们的默认值,以及over中的排序为升序。

    2.计算连续3月:

    情况1——前两月和当前月份连续

    (当前日期减前第一个月份的结果再减1)除以2)<=  30

       and

    (当前日期减前第二个月份的结果再减1)除以3)<=30 

    情况2——前一月与当前日期及后一月连续

    (当前日期减前第一个月份的结果再减1)除以2)<=  30

       and

    (后第一个月减当前日期的结果再减1)除以2)<= 30 

    情况3——后两月和当前日期连续

    (后第一个月减当前日期的结果再减1)除以2)<=30 

       and

    (后第二个月减当前日期的结果再减1)除以3)<=30 

    满足这三种情况其中之一即实现3月或以上连续

    3.根据用户id进行去重

    答疑:

    1.为什么判断连续条件要减去1  且  <= 30 ?

    答:两个连续月份之间最大的跨度为61天,如:7.1——8.31 ,我们减去1,然后除去月份个数2,刚刚好可以完成这个过滤操作。

           三个连续月份之间最大的跨度为91天,如:7.1——9.30,我们减去1,然后除去月份个数3,也是刚刚好可以完成这个过滤操作。

    如图所示

    实现源码:

      def main(args: Array[String]): Unit = {
    
    
        val spark = SparkSession.builder().appName("lianxu_month").master("local[*]").enableHiveSupport().getOrCreate()
    
    
    
        spark.read.option("header","true").csv("/home/桌面/lianxu.csv").createOrReplaceTempView("data")
    
    
        //TODO 统计出连续3月都有购买记录的用户个数
        spark.sql(
          """
             select
                count(distinct(user_id)) count
             from
                 (select
                    user_id,
                    event_date,
                    lag(event_date,2,"1970-01-01") over(distribute by user_id sort by event_date) lag_2,
                    lag(event_date,1,"1970-01-01") over(distribute by user_id sort by event_date) lag_1,
                    lead(event_date,1,"9999-99-99") over(distribute by user_id sort by event_date) lead_1,
                    lead(event_date,2,"9999-99-99") over(distribute by user_id sort by event_date) lead_2
                 from
                    (select
                      user_id,
                      to_date(event_time) event_date
                    from
                      data)t1 )t2
             where
                 (datediff(event_date,lag_1)-1)/2 <= 30 and (datediff(event_date,lag_2)-1)/3 <=30
                 or
                 (datediff(event_date,lag_1)-1)/2 <= 30 and (datediff(lead_1,event_date)-1)/2 <=30
                 or
                 (datediff(lead_1,event_date)-1)/2 <= 30 and (datediff(lead_2,event_date)-1)/3 <=30
            """.stripMargin).show()
    
    
        spark.stop()
    
    
      }

    其实无论判断连续几个月份都没关系,掌握这个运算规则就行了~

    展开全文
  • 如何写连续三个月有记录的sql

    千次阅读 2021-05-02 01:18:20
    SQL> drop table test ;Table dropped.SQL> create table test2( org_id number(6) ,3 person_id number(6),4 month_num Number(2) );Table created.SQL>SQL>SQL> insert into test values ( 1...
  • snapdate代表当天做镜像的时间,现在有需求,我们想取出来这保单号连续保持某个状态的起止时间,例如: 保单号sm1保持状态1的起止时间为2021020120210202,然后在20210203时候变成了状态2,又在20210204时候变成...
  • SQL连续增长问题--HQL面试题35

    千次阅读 2021-07-25 13:30:49
    假设我们有一张订单表shop_order shop_id,order_id,order_time,order_amt 我们需要计算过去至少3天销售金额连续增长的商户shop_id。数据如下: shop_id order_amt order_time 1 100 2021-05-10 10:03...
  • MySQL:连续个月的记录数

    千次阅读 2021-01-21 00:02:13
    我一直在寻找这,但所有类似的问题...我想计算一人买了任何“东西”的连续月份(thing01或者thing02,它不会).如果连续的purdays中断,那么计数应该重新开始.随附数据,我想最终得到:| Person | Consec Days || pers...
  • SQL Server 查询连续月份的方法

    千次阅读 2018-03-15 11:37:19
    最近一个朋友那有这么一个需求:需要查询出连续6个月有登陆的用户,并且查询的时间段可能会有跨年的情况。今天就分享一下我帮朋友解决的查询连续月份的方法。 首先模拟一个用户登陆表 CREATE TABLE UserLogin ( ...
  • 现有用户登录表(user_active_log)一份,里面有2字段:userId(用户ID),createdTime(登录时间戳),需要统计2021年12月份连续登录了7天的用户数量。 第一种方法 思路:日期列减去一列数字得到的日期相等 第一...
  • LAG函数是一常用的窗口函数,作用是取当前行之后的数据,即把该列数据向上错位。
  • SQL计算用户连续签到问题

    千次阅读 2021-08-05 20:43:20
    有一张用户签到表【t_user_attendence】,标记每天用户是否签到(说明:该表包含所有用户所有工作日的出勤记录) ,包含三个字段: 日期【fdate】 用户id【fuser_id】 用户当天是否签到【fis_sign_in:0否1是】 ...
  • 求每用户最大的连续登陆天数,断一天还算连续登录
  • 后来在SQL2003标准中加入了窗口函数,一定程度地缓解了这问题,但解决连续上涨问题仍然并不轻松。 举例子:查询连续n天上涨的股票,现有数据库表stock数据如下: code stockdate close 000062 2015...
  • Hive sql 七天内连续3天活跃sql查询

    千次阅读 2021-07-21 14:09:47
    实现思想:计算每用户本次活跃与下下次活跃相差天数 如果天数为2 则证明本次到下下为连续活跃天 步骤:-- 1 将本次活跃与下下此活跃放在同一条数据中心 – 2 计算本次活跃与下下次活跃相差天数 – 3 筛选相差...
  • SQL查询出任意连续日期或时间

    千次阅读 2019-10-09 09:56:02
    查询一年十二个月: SELECT CASE WHEN length(mon) = 1 THEN CONCAT('0', mon) ELSE mon END months FROM (SELECT @m := @m + 1 mon FROM 表名称随便, (SELECT @m := 0) ...
  • SQL】查询连续登陆7天以上的用户

    千次阅读 2021-01-19 00:24:08
    查询7天连续登陆用户这问题很经典,解决方法也有很多,这里我讲一下笔者的方法,希望对大家有帮助。具体思路:1、因为每天用户登录次数可能不止一次,所以需要先将用户每天的登录日期去重。2、再用row_number() ...
  • SQL实现筛选出连续3天登录用户与窗口函数

    千次阅读 多人点赞 2020-08-10 23:33:40
    阅读目录还原试题SQL窗口函数一.窗口函数有什么用二.什么是窗口函数.如何使用1.专用窗口函数rank2.其他专业窗口函数3.聚合函数作为窗口函数4.注意事项四.总结解题思路代码实现其他解法与延展 还原试题 首先新建...
  • 连续三天购物,就是同一用户相邻的订单日期都相差1天,比如A001用户2019/10/4、2019/10/5、2019/10/6就是连续三天购物。 可以通过日期将表自连接起来,如果以客户编号相同和日期隔一天为连接条件将表自连接,存在...
  • ![图片说明]... 数据如图所示,将时间以15分钟一点存储在数据库中,如题,比如怎么判断至少3点值大于100且这3点时间连续(如0D,0D15,0D30)?sql要怎么写
  • NULL 博文链接:https://fanshuyao.iteye.com/blog/2341163
  • 今天遇到一个问题,需要统计一个月的数据,按照每周统计出来,在网上百度了一下,目前没有百度一下,我自己想了一下,再根据网上的一点资料做参考,可以直接实现。 网上有当前日期是当年的第几周,当前日期是这周的...
  • SELECT * FROM TBL_PROB_CELL_STATS t WHERE t.STATS_TIME BETWEEN (SELECT Trunc((add_months(SYSDATE, -2)) ,'MONTH') FROM dual) AND (SELECT LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur.....
  • 获取每账户最大连续逾期期数

    千次阅读 2018-10-19 19:24:33
    lag与lead函数是跟偏移量相关的两分析函数,通过这两函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接,并且LAG...
  • SparkSQL统计——连续3天或以上

    千次阅读 2021-12-23 14:14:55
    案例实现要求:统计每月连续3天或以上的用户数 思路讲解: 1.以当前时间为基准值,获取到前面两天的时间以及后面两天的时间 通过lag和lead获取,注意它们的默认值,以及over中的排序为升.
  • 客户最近三个月点击app**模块的活跃时长 客户最近三个月点击app**模块的活跃次数 客户最近三个月点击app**模块的活跃天数 常规方法,以及存在的问题 最直接的办法就是在计算出当天数据之后,然后一次性加在90天的...
  • 最近看到一求助贴,说的是有一张考勤表,表结构为: 员工编号 NO, 上班日期 DATE ,上班状态 STATUS ( 0 正常上班 1 加班 2 迟到), 想统计所有员工最近连续加班次数,开始加班日期,结束加班日期,以及最近...
  • 连续活跃人数:连续多少天使用产品的人数 2. 次日留存与多日留存 2.1 表字段说明: 表共有四字段:uid,action,product,date uid:用户id,用户的唯一标识 action:用户在某产品中的行为 product:用户...
  • 本文介绍一经典的面试题:如果求用户的最大连续登陆天数。 题目描述:有一用户登陆表,表中有两列,一列是userid,代表用户的id;另一列是sigindate,代表用户的登陆日期。现在要求每用户的最大连续登陆天数。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 17,894
精华内容 7,157
关键字:

sql判断连续三个月