行列转换和活跃度计算

行列转换

建表

create table student2(
    id int,
    name varchar2(20),
    course varchar2(20),
    score int
);

数据：

insert into student2 values (1,'Tom','Chinese',80);
insert into student2 values (1,'Tom','Math',90);
insert into student2 values (1,'Tom','English',70);
insert into student2 values (2,'Bob','Chinese',90);
insert into student2 values (2,'Bob','Math',88);
insert into student2 values (2,'Bob','English',60);
insert into student2 values (3,'Jack','Chinese',96);
insert into student2 values (3,'Jack','Math',84);
insert into student2 values (3,'Jack','English',68);

行转列

select id,
       name,
       sum(case when course = 'Chinese' then score end) "Chinese",
       sum(case when course = 'Math' then score end)    "Math",
       sum(case when course = 'English' then score end) "English"
from student2
group by id, name;

 

将行转列代码生成新表

create table student3 as
select id,
       name,
       sum(case when course = 'Chinese' then score end) "Chinese",
       sum(case when course = 'Math' then score end)    "Math",
       sum(case when course = 'English' then score end) "English"
from student2
group by id, name;

列转行

select id, name, "Chinese" as score, 'Chinese' as course
from student3
union
select id, name, "Math" as score, 'Math' as course
from student3
union
select id, name, "English" as score, 'English' as course
from student3;

 

活跃度计算

建表

create table user_login
(
    id varchar2(255),
    login_time Date
);

数据

insert all
    INTO user_login VALUES ('A', TO_DATE('2023-06-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-01 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-01 03:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-02 04:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-03 06:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-07 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-08 22:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-09 08:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-10 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('A', TO_DATE('2023-06-11 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-01 01:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-02 02:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-03 00:50:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-04 00:50:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-05 10:20:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-06 20:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-10 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-18 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    INTO user_login VALUES ('B', TO_DATE('2023-06-20 10:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
select * from dual;

先将数据去重排序

select id, row_number() over (partition by ul.id order by ul.time) rn
from (select id, trunc(login_time) time
      from user_login
      group by id, trunc(login_time)) ul

 

然后再将时间减去排序的数字进行统计

select id, time - rn, count(time - rn)
from (select id, ul.time, row_number() over (partition by ul.id order by ul.time) rn
      from (select id, trunc(login_time) time
            from user_login
            group by id, trunc(login_time)) ul) uls
group by id, time - rn

最后就可以统计连续登录天数，或者最大登录天数

/*连续登录天数超过三天*/
select id, time - rn, count(time - rn) cnt
from (select id, ul.time, row_number() over (partition by ul.id order by ul.time) rn
      from (select id, trunc(login_time) time
            from user_login
            group by id, trunc(login_time)) ul) uls
group by id, time - rn
having count(time - rn) > 3

/*最大连续登录天数*/
select id, max(cnt)
from (select id, time - rn, count(time - rn) cnt
      from (select id, ul.time, row_number() over (partition by ul.id order by ul.time) rn
            from (select id, trunc(login_time) time
                  from user_login
                  group by id, trunc(login_time)) ul) uls
      group by id, time - rn) s
group by id