请帮忙询查用户使用了多少天

Question

sql库oracle

Answer 1

SELECT USERNAME,SDATE,EDATE,EDATE-SDATE+1 AS DAYS
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM T) 
WHERE SDATE IS NOT NULL AND EDATE IS NOT NULL

Answer 2

select username,
       sum((select to_date(min(x2.block_day), 'YYYY-MM-DD') -
               to_date(x1.resume_day, 'YYYY-MM-DD')+1
          from t x2
         where x2.username = x1.username
           and x2.block_day >= x1.resume_day)) days
  from t x1
 where username = 'abc'
   and resume_day is not null
group by username

Answer 3

谢谢两位前辈，不过小弟缺小了一点username     block_day     resume_day
abc                  2014-09-01          null
abc                          null          2014-09-27
abc                  2014-09-28          null
abc                  2014-10-01          null
abc                          null          2014-10-07
abc                  2014-10-20          null
abc                  2014-10-20          null
abc                           null          2014-10-25
abc                  2014-10-31          null
bcd                 2014-09-15          null
bcd                           null          2014-10-15
def                 2014-09-01          null请问小弟该如何询查出表里所有用户在十月里的使用天
请各位前辈赐教
谢谢。

Answer 4

SELECT USERNAME,SDATE,EDATE,
LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1 AS DAYS
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM T) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')

Answer 5

谢谢版主赐教。不过有一个问题出现表记录
username                  block_day     resume_day
0974001632                31-MAR-14  null
0974001632          31-MAR-14 null
0974001632                  null         29-AUG-14
0974001632                   15-OCT-14 null
0974001632               null                  16-OCT-14询查语句只显出 15天。
请前辈帮帮忙看看。
谢谢。

Answer 6

谢谢版主赐教。不过有一个问题出现表记录
username                  block_day     resume_day
0974001632                31-MAR-14  null
0974001632          31-MAR-14 null
0974001632                  null         29-AUG-14
0974001632                   15-OCT-14 null
0974001632               null                  16-OCT-14询查语句只显出 15天。
请前辈帮帮忙看看。
谢谢。

你的数据最后一条只能获得 16-OCT-14开始，无法获得结束日期，因此只计算了29-AUG-14到15-OCT-14部分在10月份的天数
SELECT USERNAME,SDATE,EDATE,
LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1 AS DAYS
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    select username,block_day,resume_day
    from TABLE
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')

Answer 7

这的很感谢前辈。不好意思，小弟刚发现个问题username                  block_day     resume_day    cancel_date
0974001632                   31-MAR-14     null
0974001632             31-MAR-14    null
0974001632                         null         29-AUG-14
0974001632                      15-OCT-14    null
0974001632                      null                  16-OCT-14
0974001632                      null                  null                20-OCT-14如果 resume 后 过几天用户就取消了，那使用天就计算到取消当天。
或者 block 后 过几天用户就取消了， 那使用天就计算到block的日子。请前辈赐教询查语句。
谢谢。

Answer 8

nvl(cancel_date,block_date)替换语句中的block_date
SELECT USERNAME,SDATE,EDATE,
LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1 AS DAYS
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    select username,nvl(cancel_date,block_date) block_day,resume_day
    from TABLE
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')

Answer 9

谢谢前辈赐教！不过 应该是替换 block_day 不是 block_date 对吗？谢谢前辈！

Answer 10

前辈好，请前辈赐教下，小弟想那所有用户在十月的使用天，
但如果用户在十月前已经给block了，在十月里没有resume，使用天就显示 “0”。请前辈赐教。
谢谢。

Answer 11

WITH T1 AS (
SELECT USERNAME,sum(LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1) AS DAYS FROM
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    select username,nvl(cancel_day,block_day) block_day,resume_day
    from TABLE
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')
GROUP BY USERNAME),
T2 AS (SELECT DISTINCT USERNAME FROM T)
SELECT T2.USERNAME,NVL(T1.DAYS,0) FROM T1 RIGHT JOIN T2 ON T1.USERNAME=T2.USERNAME

Answer 12

真的谢谢前辈赐教。小弟自己试试写了，比较长，但是不知道对吗，请前辈过目赐教。谢谢
SELECT MEMBER_ID ,SUM((CASE WHEN DAYS >=0 AND DAYS IS NOT NULLTHEN DAYSWHEN DAYS IS NULL THEN 0ELSE 0END ))DAYS
FROM
(
SELECT USERNAME,SDATE,EDATE,
SUM(LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1) AS DAYS
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    select username,nvl(cancel_date,block_date) block_day,resume_day
    from TABLE
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE GROUP BY USER_NAME
)

Answer 13

1、WHERE GROUP BY USER_NAME 把where去了
2、(CASE WHEN DAYS >=0 AND DAYS IS NOT NULL
THEN DAYS
WHEN DAYS IS NULL 
THEN 0
ELSE 0
END )
改为nvl(days,0)
3、整体没啥问题，但效率较差

Answer 14

谢谢前辈赐教。
小弟刚刚运行语句，发现
如果用户 重来没给block过或者以resume在十月前，但是使用天显示也是"0"
应该是 ”31“。
请前辈赐教。
谢谢。

Answer 15

小弟刚刚运行语句，发现
如果用户 重来没给block过或者以resume在十月前，但是使用天显示也是"0"
应该是 ”31“。
请前辈赐教。
谢谢。继续上面的问题，如果表里加了pause_day
username                  block_day     resume_day    cancel_date        pause_day
0974001632                      10-OCT-14    null                null                     null
0974001632                      null                  12-OCT-14   null                    null
0974001632                      null                  null               null                    20-OCT-14
0974001632                      null                  null               29-OCT-14                   null假如用户block在 10-OCT 在 12-OCT resume 用户从十月头到 12 号使用 10 天
从20-OCT用户暂停（暂停依然计算是用户使用天）
到了29-OCT用户取消了。计算用户使用天是 ： 26 天。请前辈赐教询查语句
谢谢。

Answer 16

26咋算出来的啊，1～10 12～29 这一共是28天啊
按目前逻辑pause_day这些记录对最后结果没影响，直接在查询条件中加上pause_day is null 就可以了

Answer 17

麻烦前辈可以给个示范语句吗？
还有如果用户暂停(pause)然后resume然后停用（block）然后resume
那对询查结果有影响吗？还有之前小弟有提到
如果用户 重来没给block过或者以resume在十月前，但是使用天显示也是"0"请前辈赐教。
谢谢。

Answer 18

单用一条语句查出来可能比较难，写个函数，在函数里用游标进行累加会简单一点oracle 三四年不用了，机器上没装过，无法调试，只能给个建议，楼主见谅

Answer 19

不好意思，小弟算错，28天是对的。请前辈赐教。还有一个问题
如果用户 重来没给block过或者以resume在十月前，但是使用天显示也是"0"
应该是 ”31“。
请前辈赐教。
谢谢。

Answer 20

WITH T1 AS (
SELECT USERNAME,sum(LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1) AS DAYS FROM
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    select username,nvl(cancel_day,block_day) block_day,resume_day
    from TABLE
    where pause_day is null
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')
GROUP BY USERNAME),
T2 AS (SELECT DISTINCT USERNAME FROM T)
SELECT T2.USERNAME,NVL(T1.DAYS,
(case when
    not exists (select 1 from table
        where username=t2.username and nvl(block_day,resume_day)<=TO_DATE('2014-10-31','YYYY-MM-DD'))
then 31 else 0 end)
)
FROM T1 RIGHT JOIN T2 ON T1.USERNAME=T2.USERNAME

Answer 21

前辈好，小弟运行语句不过以下的用户只显示 “8”天
应该是17天。username                  block_day     resume_day    cancel_date        pause_day
bn24421102                  null                  null                null                     null
bn24421102                      null                   null                null                 25-JUL-14
bn24421102                        null          01-SEP-14            null                    null
bn24421102                       null                  null               null              02-SEP-14
bn24421102                        null          10-SEP-14            null                    null
bn24421102                        null                  null           null                   17-OCT-14请前辈赐教。
谢谢。

Answer 22

1号到10号之间没有cancel或block的记录，1号的那条记录就被忽略了，
只计算了10到17，需要增加条件把10-SEP-14这条记录过滤掉
另外，你这里面咋还有4个日期都为空的记录
ps：你这问问题的基本可以拍连续剧了

Answer 23

不好意思，前辈见谅
小弟是根据公司前个人员写下的语句，在根据公司现在需要的情况改写。因为小弟经验有限，请前辈见谅小弟刚刚那到资料库询查限权，那个表示用来记录用户使用历史
STATUS     MEMBER     LOG_DATE
REG     bn24421102     23-JUL-14
PAUSE     bn24421102     25-JUL-14
RESUM          bn24421102     01-SEP-14
PAUSE     bn24421102     02-SEP-14
RESUM     bn24421102     10-OCT-14
CANCEL     bn24421102     17-OCT-14
之前人家写成
username                  block_day     resume_day    cancel_date        pause_day
bn24421102                  null                  null                null                     null
bn24421102                      null                   null                null                 25-JUL-14
bn24421102                        null          01-SEP-14            null                    null
bn24421102                       null                  null               null              02-SEP-14
bn24421102                        null          10-SEP-14            null                    null
bn24421102                        null                  null           null                   17-OCT-14情况
每个月都要计算用户使用天
如果停用（block)的期间，不计算使用天直到resume
如果暂停 依然计算使用天
如果取消，使用天就计算到取消那天。
请前辈赐教。

Answer 24

STATUS 里有个“REG“是代表用户登记使用。

Answer 25

STATUS 里有个“REG“是代表用户登记使用。不好意思前辈，10-SEP-14 是小弟写错，应该是 10-OCT-14

Answer 26

尽量使用原始表进行统计
resume记录作为使用开始日期
block和cancel记录作为使用结束日期
reg和pause记录忽略掉
连续的两次开始取前一次的开始日期是否有连续两次结束的状况？比如 cancel完再block之类的

Answer 27

没有了，如果用户取消了（CANCEL），就不会再有任何状态，直到用户从新登记（REG)
请前辈赐教。
谢谢。

Answer 28

不过也有情况出现，
用户暂停(PAUSE)然后停用(BLOCK)然后取消（CANCEL)     //这情况下没有RESUME记录但是也有使用天
用户暂停(PAUSE)然后停用(BLOCK)然后RESUME 然后取消（CANCEL)   
//这情况下从暂停（PAUSE)到（BLOCK)有使用天,BLOCK然后RESUME后也会有使用天。请前辈赐教询查语句。
谢谢

Answer 29

WITH T1 AS (
SELECT USERNAME,sum(LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1) AS DAYS FROM
(select username,lag(resume_day,1,null)over(partition by user_name order by nvl(block_day,resume_day)) SDATE,block_day EDATE
FROM (
    SELECT MEMBER USERNAME,PRE_DATE SDATE,LOG_DATE EDATE
    FROM(
        SELECT STATUS,MEMBER,LOG_DATE,LAG(LOG_DATE,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_DATE
        FROM(
            SELECT STATUS,MEMBER,LOG_DATE,LAG(STATUS,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_STATUS
            FROM T
        )WHERE DECODE(STATUS,'RESUME',1,'PAUSE',1,'BLOCK',2,'CANCEL',2,3)
             <>DECODE(PRE_STATUS,'RESUME',1,'PAUSE',1,'BLOCK',2,'CANCEL',2,3)
    )WHERE STATUS IN ('BLOCK','CANCEL')
    UNION ALL
    SELECT DISTINCT USER_NAME,TRUNC(SYSDATE),NULL FROM  TABLE
)T
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')
GROUP BY USERNAME),
T2 AS (SELECT DISTINCT USERNAME FROM T)
SELECT T2.USERNAME,NVL(T1.DAYS,
(case when
    not exists (select 1 from table
        where username=t2.username and nvl(block_day,resume_day)<=TO_DATE('2014-10-31','YYYY-MM-DD'))
then 31 else 0 end)
)
FROM T1 RIGHT JOIN T2 ON T1.USERNAME=T2.USERNAME改用原始表统计了，试一下吧
ps：你这30分……，再改需求不管了

Answer 30

真的感谢前辈！小弟经验小，请前辈见谅。小弟在语句里看见有 block_day,resume_day 
是否要改成别的？？？因为小弟在运行里， block_day,resume_day 找不到。再次麻烦前辈帮忙看看。
谢谢。

Answer 31

请问前辈前辈用的T ，TABLE,table 是哪个表啊？
小弟用些乱！请前辈赐教！
谢谢。

Answer 32

WITH T1 AS (
SELECT USERNAME,sum(LEAST(EDATE,TO_DATE('2014-10-31','YYYY-MM-DD'))-GREATEST(SDATE,TO_DATE('2014-10-01','YYYY-MM-DD'))+1) AS DAYS FROM
(
    SELECT MEMBER USERNAME,PRE_DATE SDATE,LOG_DATE EDATE
    FROM(
        SELECT STATUS,MEMBER,LOG_DATE,LAG(LOG_DATE,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_DATE
        FROM(
            SELECT STATUS,MEMBER,LOG_DATE,LAG(STATUS,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_STATUS
            FROM T
            UNION ALL
            SELECT 'BLOCK',MEMBER,TRUNC(SYSDATE),MAX(LOG_DATE),MAX(STATUS)KEEP(DENSE_RANK LAST ORDER BY LOG_DATE,ROWID)
            FROM  T GROUP BY MEMBER
        )WHERE DECODE(STATUS,'RESUME',1,'PAUSE',1,'BLOCK',2,'CANCEL',2,3)
             <>DECODE(PRE_STATUS,'RESUME',1,'PAUSE',1,'BLOCK',2,'CANCEL',2,3)
    )WHERE STATUS IN ('BLOCK','CANCEL')
) 
WHERE SDATE<=TO_DATE('2014-10-31','YYYY-MM-DD') AND EDATE>=TO_DATE('2014-10-01','YYYY-MM-DD')
GROUP BY USERNAME),
T2 AS (SELECT DISTINCT USERNAME FROM T)
SELECT T2.USERNAME,NVL(T1.DAYS,
(case when
    not exists (select 1 from table
        where username=t2.username and status in ('BLOCK','RESUME') and log_date<=TO_DATE('2014-10-31','YYYY-MM-DD'))
then 31 else 0 end)
)
FROM T1 RIGHT JOIN T2 ON T1.USERNAME=T2.USERNAME

Answer 33

T ，TABLE,table 这些都指得是你的原始表，由于语句太长，没统一起来，你自己改下吧

Answer 34

 SELECT STATUS,MEMBER,LOG_DATE,LAG(LOG_DATE,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_DATE
        FROM(
            SELECT STATUS,MEMBER,LOG_DATE,LAG(STATUS,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_STATUS
            FROM T
            UNION ALL
            SELECT 'BLOCK',MEMBER,TRUNC(SYSDATE),MAX(LOG_DATE),MAX(STATUS)KEEP(DENSE_RANK LAST ORDER BY LOG_DATE,ROWID)
            FROM  T GROUP BY MEMBER前辈好，根据上面的语句
UNION ALL 里的column 不相对 （ 4 比 5）
请前辈赐教！
谢谢。

Answer 35

提供下执行计划，根据执行计划建立相应的索引提升查询速度，语句方面大部分使用的分析函数，优化的空间不大目前没执行计划，建议建立MEMBER,LOG_DATE索引先试下
建索引你不一定有权限啊，唉，不行的话可以考虑建立物化视图

Answer 36

not exists (select 1 from table
        where username=t2.username and status in ('BLOCK','RESUME') and log_date<=TO_DATE('2014-10-31','YYYY-MM-DD'))
这个条件用分析函数实现应该速度还能提升
不过我要下班了，准备去过周末了，呵呵
你可以自己先试试，不行下周帮你写
ps：这么长的回复也不知道给加点分

Answer 37

前辈好，小弟有个问题
在“UNION ALL"的两句语句里
 SELECT STATUS,MEMBER,LOG_DATE,LAG(STATUS,1,null)OVER(PARTITION BY MEMBER ORDER BY LOG_DATE) PRE_STATUS
            FROM T
            UNION ALL
            SELECT 'BLOCK',MEMBER,TRUNC(SYSDATE),MAX(LOG_DATE),MAX(STATUS)KEEP(DENSE_RANK LAST ORDER BY LOG_DATE,ROWID)
            FROM  T GROUP BY MEMBER
小弟看看好像是多出了 ”TRUNC(SYSDATE)“ 运行语句是出错。
小弟尝试删掉”TRUNC(SYSDATE)“ 语句运行了。
小弟这样做有错吗？还有个问题如果用户只是登记，从来没有停用，暂停，或RESUME 
那个用户的使用天就给计算”0“。应该是”31“请前辈赐教。
谢谢。

Answer 38

谢谢前辈。不过有些用户登记了，然后在十月之前取消(cancel)。
不过运行语句也出现使用了"31"天。请前辈赐教。
谢谢。

Answer 39

(case when
    not exists (select 1 from table
        where username=t2.username and status in ('BLOCK','RESUME') and log_date<=TO_DATE('2014-10-31','YYYY-MM-DD'))
    and NOT exists (select 1 from table
        where username=t2.username and status='CANCEL' and log_date<TO_DATE('2014-10-01','YYYY-MM-DD'))
then 31 else 0 end)
)

Answer 40

请问前辈关于优化询查速度，小弟该如何做，因为运行语句是，前50 rows 已经用了十秒。数据表的数据大概80万。请前辈赐教。
谢谢。

Answer 41

不好意思，小弟的意思是
用户登记了，然后停用（block）在十月底 （2014-OTC-30)
那么使用天应该是30天，小弟运行语句，它显示使用天是"0".请前辈赐教。
谢谢。