Skip to content

Instantly share code, notes, and snippets.

@ufo22940268

ufo22940268/newline.sql

Created April 26, 2018 09:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ufo22940268/7aeb2e7591b83a48a40e42bc7411e869 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ufo22940268/7aeb2e7591b83a48a40e42bc7411e869 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ufo22940268 created this gist Apr 26, 2018.
    204 changes: 204 additions & 0 deletions newline.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,204 @@
    SELECT date,id,name,if(defalutinterest_num is null,0,defalutinterest_num),
    if(one_unsubscribe_num is null,0,one_unsubscribe_num),
    if(three_unsubscribe_sum is null,0,three_unsubscribe_sum),
    if(seven_unsubscribe_sum is null,0,seven_unsubscribe_sum),
    if(avg_quguan_rate is null,0,avg_quguan_rate),
    if(next_active_rate is null,0,next_active_rate),
    if(valid_num is null,0,valid_num),
    if(valid_num /defalutinterest_num is null,0,valid_num /defalutinterest_num),
    if(nextread_num is null,0,nextread_num),
    if(nextread_num/valid_num is null,0,nextread_num/valid_num),
    if(interest_num is null,0,interest_num),
    if(interest_sum is null,0,interest_sum),
    if(sumtotalprice is null,0,sumtotalprice),
    if(dan_num is null,0,dan_num),
    if(sumtotalprice/dan_num is null,0,sumtotalprice/dan_num),
    if(ke_num is null,0,ke_num),
    if(sumtotalprice/ke_num is null,0,sumtotalprice/ke_num),
    if(newdefault_sumtotalprice is null,0,newdefault_sumtotalprice),
    if(newdefault_dan_num is null,0,newdefault_dan_num),
    if(newdefault_sumtotalprice/newdefault_dan_num is null,0,newdefault_sumtotalprice/newdefault_dan_num),
    if(newdefault_ke_num is null,0,newdefault_ke_num),
    if(newdefault_sumtotalprice/newdefault_ke_num is null,0,newdefault_sumtotalprice/newdefault_ke_num)

    FROM
    (
    SELECT id,name,date
    FROM
    (SELECT id,name
    FROM channels
    WHERE launched = true
    )
    CROSS JOIN
    (SELECT date(time) as date
    FROM orders
    WHERE date(time) = date'2018-04-20'
    GROUP BY 1
    )
    )aaa
    LEFT JOIN
    ( SELECT a.channel,count(a.user) as defalutinterest_num,
    count(b.user) as one_unsubscribe_num,
    count(c.user) as three_unsubscribe_sum,
    count(d.user) as seven_unsubscribe_sum
    FROM
    (SELECT channel,user
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)a
    LEFT JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND action = 'CHANNEL_UNSUBSCRIBE'
    GROUP BY 1,2)b
    ON a.user = b.user AND a.channel = b.channel
    LEFT JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date between date'2018-04-20' AND date'2018-04-22'
    AND action = 'CHANNEL_UNSUBSCRIBE'
    GROUP BY 1,2)c
    ON a.user = c.user AND a.channel = c.channel
    LEFT JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date between date'2018-04-20' AND date'2018-04-26'
    AND action = 'CHANNEL_UNSUBSCRIBE'
    GROUP BY 1,2)d
    ON a.user = d.user AND a.channel = d.channel
    GROUP BY 1
    )bbb
    ON aaa.id = bbb.channel
    LEFT JOIN
    (
    SELECT a.channel,count(b.user) as valid_num ,count(c.user) as nextread_num
    FROM
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)a
    JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND action = 'REVIEW_ENTER'
    GROUP BY 1,2)b
    ON a.user =b.user AND a.channel = b.channel
    LEFT JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-21'
    AND action = 'REVIEW_ENTER'
    GROUP BY 1,2)c
    ON a.user = c.user and a.channel = c.channel
    GROUP BY 1
    )ccc
    ON aaa.id = ccc.channel
    LEFT JOIN
    (SELECT channel,count(distinct user) as interest_num
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1)ddd
    ON aaa.id = ddd.channel
    LEFT JOIN
    (SELECT channel,sum(`subscribeÇount`) as interest_sum
    FROM channel_subscribe_history
    WHERE date = date'2018-04-20'
    GROUP BY 1)eee
    ON aaa.id = eee.channel
    LEFT JOIN
    (SELECT channel,sum(totalprice) as sumtotalprice,
    count(*) as dan_num ,count(distinct buyer) as ke_num
    FROM orders
    WHERE paid = true
    AND date(time) = date'2018-04-20'
    GROUP BY 1)fff
    ON aaa.id = fff.channel
    LEFT JOIN
    (SELECT b.channel,sum(totalprice) as newdefault_sumtotalprice,
    count(*) as newdefault_dan_num ,count(distinct buyer) as newdefault_ke_num
    FROM
    (SELECT id
    FROM users
    WHERE date(time) = date'2018-04-20'
    AND role is NULL)a
    JOIN
    (SELECT id,buyer,totalprice,channel
    FROM orders
    WHERE paid = true
    AND date(time) = date'2018-04-20')b
    ON a.id = b.buyer
    JOIN
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)c
    ON b.buyer = c.user AND b.channel = c.channel
    GROUP BY 1)hhh
    ON aaa.id = hhh.channel
    LEFT JOIN
    (SELECT channel,sum(quguan)/6 as avg_quguan_rate
    FROM
    (SELECT aa.date,aa.channel,unsubscribe_num/liucun_num as quguan
    FROM
    (SELECT date,a.channel,count(a.user) as unsubscribe_num
    FROM
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)a
    JOIN
    (SELECT date,user,channel
    FROM userlogs
    WHERE date between date'2018-04-21' and date'2018-04-26'
    AND action = 'CHANNEL_UNSUBSCRIBE'
    GROUP BY 1,2,3)b
    ON a.user = b.user and a.channel= b.channel
    GROUP BY 1,2)aa
    JOIN
    (SELECT date,a.channel,count(distinct b.user) as liucun_num
    FROM
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)a
    JOIN
    (SELECT date,user,channel
    FROM userlogs
    WHERE date between date'2018-04-21' and date'2018-04-26'
    GROUP BY 1,2,3)b
    ON a.user = b.user
    GROUP BY 1,2)bb
    ON aa.date = bb.date AND aa.channel =bb.channel)aaa
    GROUP BY 1)aaaa
    ON aaa.id = aaaa.channel
    LEFT JOIN
    (SELECT a.channel,count(a.user) as today_num,count(b.user) as next_active_num,
    count(b.user) / count(a.user) as next_active_rate
    FROM
    (SELECT user,channel
    FROM userlogs
    WHERE date = date'2018-04-20'
    AND batchsubscribe = true
    AND action = 'CHANNEL_SUBSCRIBE'
    GROUP BY 1,2)a
    LEFT JOIN
    (SELECT user
    FROM userlogs
    WHERE date = date'2018-04-21'
    GROUP BY 1)b
    ON a.user = b.user
    GROUP BY 1)bbbb
    ON aaa.id =bbbb.channel