mysql hive sql 進階

weixin_34292959發表於2017-09-05
MySqlHive

場景:

說明.1.上面的資料是經過規整的資料,step是連續的,這個可以通過row_number實現.連續是必要的一個條件因為在計算第二個查詢條件時依賴這個順序,如果step不是數字欄位可以擷取然後轉為數字

1、查詢每一步驟相對於路徑起點人數的比例

2、查詢每一步驟相對於上一步驟的漏出率

說明1.step=1時為起點.

2.以上需求是要在hive中實現的,但是麼有資料就就再mysql中實現,sql大致一樣

3.sql在mysql下測試都是通過的

| CREATE TABLE `step1` (
  `id` int(11) DEFAULT NULL,
  `numbs` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
select a.id,a.numbs ,a.numbs/(select c.numbs from step1 c where c.id = (select min(id) from step1 b) ),a.numbs/d.numbs from step1 a left join step1 d on d.id  = a.id -1 
order by a.id asc

思路:1.求出第一步

select c.numbs from step1 c where c.id = (select min(id) from step1 b),
2求出上一步
left join step1 d on d.id  = a.id -1 
order by a.id asc
 
第二種實現
select  a.id as step,a.numbs ,a.numbs/z.numbs , a.numbs/c.numbs   from step1 a 
inner join (select y.minid, x.numbs from (select min(id) minid from step1 b) y join step1 x  on x.id = y.minid) z  
left join step1 c on a.id-1 = c.id group by a.id asc;

由"查詢每一步驟相對於路徑起點人數的比例"可知,每一行的資料都要和起點行的資料相除.那麼,就是每一行都要有起點行的資料,此時當資料表有多行,起始資料只有一行時,他們再inner join一下,就達到目的了

之後再進行相除就很簡單了

 

場景2 : 求每月的訪問量,截止到當月的每個使用者的總訪問量

可能適用的一個場景,之前在工作中遇到需求,就是要對一個資料進行累加,累加是按月的,比如,1月31日統計之後1月就停止統計,然後將1月31日的值繼續計算2月的.這樣一直計算,到每個月的最後一天這個月的統計就結束.

解決方法,級聯求和,二次求和,第一次已經聚合了,第二次聚合時使用max函式,或者min函式對上次的資料進行一次"聚合"因為第一次已經聚合過了,這一列只有一個值,所以聚合之後還是一樣的結果.

資料

A    2015-01    5
A    2015-01    15
B    2015-01    5
A    2015-01    8
B    2015-01    25
A    2015-01    5
A    2015-02    4
A    2015-02    6
B    2015-02    10
B    2015-02    5
 CREATE TABLE `t_access` (
  `vistor` varchar(255) DEFAULT NULL,
  `ymonth` varchar(255) DEFAULT NULL,
  `vistTimes` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

結果:

select A.vistor,A.ymonth,max(A.vistTimes) as vistTimes,sum(B.vistTimes) as accumulate
from 
(select vistor,ymonth,sum(vistTimes) as vistTimes from t_access group by vistor,ymonth) A 
inner join 
(select vistor,ymonth,sum(vistTimes) as vistTimes from t_access group by vistor,ymonth) B
on
A.vistor=B.vistor
where B.ymonth <= A.ymonth
group by A.vistor,A.ymonth
order by A.vistor,A.ymonth;

select * from t_access;
select aa.vistor,aa.ymonth ,max(aa.sumvistTimes),sum(b.sumvistTimes) 
from (select a.vistor,a.ymonth ,SUM(a.vistTimes) as sumvistTimes from t_access a group by a.vistor,a.ymonth ) aa 
left join  
(select m.vistor,m.ymonth ,SUM(m.vistTimes) as sumvistTimes from t_access m group by m.vistor,m.ymonth )
 b on    b.vistor = aa.vistor  where aa.ymonth >= b.ymonth GROUP BY aa.vistor,aa.ymonth

總結,上面的兩個場景都用到了inner join ,尤其在第一種實現時,感覺提高了查詢效能,(沒有測試過).hive只支援等值的join

 

相關文章