统计

 

-- Create table
create table A_ORIGINAL
(
  qh       VARCHAR2(20),
  kai_date VARCHAR2(20),
  hong     VARCHAR2(50),
  lan      VARCHAR2(10)
);

-- Create table
create table A_INFO
(
  qh       VARCHAR2(20),
  kai_date DATE,
  one      NUMBER,
  two      NUMBER,
  there    NUMBER,
  four     NUMBER,
  five     NUMBER,
  six      NUMBER,
  lan      NUMBER
);

-- Create table
create table A_COUNT
(
  orderno     NUMBER,
  one         NUMBER,
  one_count   NUMBER,
  two         NUMBER,
  two_count   NUMBER,
  there       NUMBER,
  there_count NUMBER,
  four        NUMBER,
  four_count  NUMBER,
  five        NUMBER,
  five_count  NUMBER,
  six         NUMBER,
  six_count   NUMBER,
  lan         NUMBER,
  lan_count   NUMBER
);

create table A_COUNT_TMP
(
  orderno     NUMBER,
  one         NUMBER,
  one_count   NUMBER,
  two         NUMBER,
  two_count   NUMBER,
  there       NUMBER,
  there_count NUMBER,
  four        NUMBER,
  four_count  NUMBER,
  five        NUMBER,
  five_count  NUMBER,
  six         NUMBER,
  six_count   NUMBER,
  lan         NUMBER,
  lan_count   NUMBER
);

insert into a_info
                            (qh, kai_date, one, two, there, four, five, six, lan)
                       
                          select t.qh,to_date(t.kai_date,'yyyy-MM-dd'),substr(hong,0,2) one,substr(hong,3,2)two,substr(hong,5,2)there,substr(hong,7,2)four,substr(hong,9,2)five,substr(hong,11,2)six,substr(hong,length(hong)-1,2)six
               from A_ORIGINAL t;

 

select t.one,count(1)oneCount from a_info t group by t.one order by count(1) desc;
select t.two,count(1)twoCount from a_info t group by t.two order by count(1) desc;
select t.there,count(1)thereCount from a_info t group by t.there order by count(1) desc;
select t.four,count(1)fourCount from a_info t group by t.four order by count(1) desc;
select t.five,count(1)fiveCount from a_info t group by t.five order by count(1) desc;
select t.six,count(1)sixCount from a_info t group by t.six order by count(1) desc;
select t.lan,count(1)lanCount from a_info t group by t.lan order by count(1) desc;

--第一条要选择行数最多的那条
insert into a_count (ORDERNO,there,there_count)
select rownum,t.* from
(
select t.there,count(1)thereCount from a_info t group by t.there order by count(1) desc
)t;

truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,one,one_Count)
select rownum,t.* from
(
select t.one,count(1)one_Count from a_info t group by t.one order by count(1) desc
)t;

update a_count a set (a.one,a.one_count) =(
 select t.one,t.one_count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);


truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,two,two_Count)
select rownum,t.* from
(
select t.two,count(1)two_Count from a_info t group by t.two order by count(1) desc
)t;

update a_count a set (a.two,a.two_Count) =(
 select t.two,t.two_Count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);


truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,four,four_Count)
select rownum,t.* from
(
select t.four,count(1)four_Count from a_info t group by t.four order by count(1) desc
)t;

update a_count a set (a.four,a.four_Count) =(
 select t.four,t.four_Count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);

truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,five,five_Count)
select rownum,t.* from
(
select t.five,count(1)five_Count from a_info t group by t.five order by count(1) desc
)t;

update a_count a set (a.five,a.five_Count) =(
 select t.five,t.five_Count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);


truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,six,six_Count)
select rownum,t.* from
(
select t.six,count(1)six_Count from a_info t group by t.six order by count(1) desc
)t;

update a_count a set (a.six,a.six_Count) =(
 select t.six,t.six_Count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);


truncate table a_count_TMP;
insert into a_count_TMP (ORDERNO,lan,lan_Count)
select rownum,t.* from
(
select t.lan,count(1)lan_Count from a_info t group by t.lan order by count(1) desc
)t;

update a_count a set (a.lan,a.lan_Count) =(
 select t.lan,t.lan_Count from a_count_TMP t 
 where t.orderno =a.orderno
)where exists(
select 1 from a_count_TMP  where orderno =a.orderno
);

select * from a_count;
select * from a_info;
select * from a_original;

 select    
 
    one, count(1) over(partition by one) onecount,
    two ,count(1)over(partition by two) twocount,
        there ,count(1)over(partition by two) therecount,
            four ,count(1)over(partition by two) fourcount,
                five ,count(1)over(partition by two) fivecount,
                    six ,count(1)over(partition by two) sixcount,
                        lan ,count(1)over(partition by two) lancount    

  from a_info;