sql笔记

1. 10分钟之内在超过两个医院就诊

--明细
create table lu_tmp as
select t.*, row_number() over(partition by aac147,ymd order by opdate) row_num
from shen_zx_mdps t where ym = '2018-12'

--查一天内重复的
create table lu_tmp1 as
select aac147,opdate,hicode from lu_tmp 
where (aac147,ymd) in (select aac147,ymd from lu_tmp group by aac147,ymd having count(distinct hicode)>2)
group by aac147,opdate,hicode

--查明细
select a.* from lu_tmp a, (
  select a.aac147,a.opdate,a.hicode from lu_tmp1 a, 
  (
         select * 
         from lu_tmp1 t  
         where (
           select count(distinct hicode) 
           from lu_tmp1 
           where aac147=t.aac147 
           and opdate>=t.opdate 
           and opdate<t.opdate+1/144
         )>2
  ) b where a.aac147=b.aac147 and a.opdate>=b.opdate and a.opdate<b.opdate+1/144
) b
where a.aac147=b.aac147 and a.opdate=b.opdate and a.hicode=b.hicode
order by a.aac147,a.opdate,a.hicode

--测试
select aac147,opdate,hicode,hiname from lu_tmp 
where aac147='xx' 
and opdate >= to_date('2018/12/7 10:16:03','yyyy-mm-dd hh24:mi:ss')
and opdate < to_date('2018/12/7 10:16:03','yyyy-mm-dd hh24:mi:ss')+1/144

　　2. 查询连续登陆7天以上的用户

1. 排序
2. 日期减序号
3. 人，日期分组统计，大于7
4. 筛选明细

select id,count(*) 
from 
(select *,date_add(dated,-rown) as startdate 
    from
         (select *,row_number() over(PARTITION by id order by dated) as rown
             from 
                (select distinct id, to_date(date) as dated 
                      from table ) a
          ) b
)c 
GROUP BY id,startdate
having count(*)>=7;

　　3.找出同一组内值都相等，都不等，占比超过80%的组及对应的值

--判断同一组内所有值是否都相等，输出都相等且元素个数大于3的组
select id
from tmp 
group by id
where count(distinct value)=1 and count(1)>3

select id
from (
select id, value, count(1) counts
from tmp t
group by id, value
) t
group by id
having count(1)=1 and sum(counts)>3


--找出同一组内所有值都不相等且元素个数大于3的组
select id
from tmp t
group by id
where count(1)=count(distinct value)


--找出同一组内有超过80%数据相同的组及对应的值
select id
from (
select id, value, count(1) counts
from tmp t
group by id, value
) t
group by id
having max(counts)/sum(counts)>0.8


select a.id, a.values, a.counts/b.counts as rate
from (
select id, value, count(1) counts
from tmp t
group by id, value
) a, (
select id, count(1) counts
from tmp t
group by id
) b
where a.id=b.id
and a.counts/b.counts>0.8

　　4. 字符串拆分及入库

--函数使用示例
SELECT
    REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM
    DUAL  
CONNECT BY
    LEVEL <= 3  
	-- LENGTH(REGEXP_REPLACE('17,20,23', '[^,]+')) + 1
	-- LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1

--建表
--drop table SalesList;
create table SalesList(
    keHu                varchar2(20),   --客户
    shangPin            varchar2(20),   --商品名称
    salesNum            number(8)       --销售数量
);
  
--插入数据
declare
  --谈几个客户
  cursor lr_kh is
  select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual
   connect by level <= 4;
  --进点货
  cursor lr_sp is
  select regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual
   connect by level <= 4;
begin
  --循环插入
  for v_kh in lr_kh loop
     for v_sp in lr_sp loop
        insert into SalesList
        select v_kh.keHu, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
     end loop;
  end loop;
  commit;
end;