SHARE
TWEET

Untitled

a guest Jan 20th, 2020 79 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.         SELECT clean_source,
  2.                sumIf(total, clean_demographic = '')                AS total,
  3.  
  4.                sumIf(female_percent, clean_demographic = '')       AS female_percent,
  5.        
  6.                sumIf(pos_percent, clean_demographic = 'female')    AS female_pos_percent,
  7.                sumIf(neu_percent, clean_demographic = 'female')    AS female_neu_percent,
  8.                sumIf(neg_percent, clean_demographic = 'female')    AS female_neg_percent,
  9.        
  10.                sumIf(male_percent, clean_demographic = '')         AS male_percent,
  11.                sumIf(pos_percent, clean_demographic = 'male')      AS male_pos_percent,
  12.                sumIf(neu_percent, clean_demographic = 'male')      AS male_neu_percent,
  13.                sumIf(neg_percent, clean_demographic = 'male')      AS male_neg_percent,
  14.        
  15.                sumIf(community_percent, clean_demographic = '')    AS community_percent,
  16.                sumIf(pos_percent, clean_demographic = 'community') AS community_pos_percent,
  17.                sumIf(neu_percent, clean_demographic = 'community') AS community_neu_percent,
  18.                sumIf(neg_percent, clean_demographic = 'community') AS community_neg_percent,
  19.        
  20.                sumIf(post_percent, post_type = '')                 AS post_percent,
  21.                sumIf(pos_percent, post_type = 'socialmedia')       AS post_pos_percent,
  22.                sumIf(neu_percent, post_type = 'socialmedia')       AS post_neu_percent,
  23.                sumIf(neg_percent, post_type = 'socialmedia')       AS post_neg_percent,
  24.        
  25.                sumIf(repost_percent, post_type = '')               AS repost_percent,
  26.                sumIf(pos_percent, post_type = 'repost')            AS repost_pos_percent,
  27.                sumIf(neu_percent, post_type = 'repost')            AS repost_neu_percent,
  28.                sumIf(neg_percent, post_type = 'repost')            AS repost_neg_percent,
  29.        
  30.                sumIf(comment_percent, post_type = '')              AS comment_percent,
  31.                sumIf(pos_percent, post_type = 'comment')           AS comment_pos_percent,
  32.                sumIf(neu_percent, post_type = 'comment')           AS comment_neu_percent,
  33.                sumIf(neg_percent, post_type = 'comment')           AS comment_neg_percent,
  34.                
  35.                sumIf(pos_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_pos_percent,
  36.                sumIf(neu_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_neu_percent,
  37.                sumIf(neg_percent, clean_demographic = '' AND aggregation_type = 0) AS overall_neg_percent
  38.         FROM (
  39.               SELECT CleanSource                                                                      AS clean_source,
  40.                      COUNT(Demographic)                                                               AS total,
  41.                      CleanDemographic                                                                 AS clean_demographic,
  42.                      round(countIf(clean_demographic = 'female') / total * 100)                       AS female_percent,
  43.                      round(countIf(clean_demographic = 'male') / total * 100)                         AS male_percent,
  44.                      round(countIf(clean_demographic = 'community') / total * 100)                    AS community_percent,
  45.                      round(countIf(Sentiment = 'pos') / total * 100)                                  AS pos_percent,
  46.                      round(countIf(Sentiment = 'neg') / total * 100)                                  AS neg_percent,
  47.                      round(countIf(Sentiment = 'neu') / total * 100)                                  AS neu_percent,
  48.                      CAST('', 'Enum8(\\'\\' = 0, \\'comment\\' = 1, \\'repost\\' = 2, \\'socialmedia\\' = 3)') AS post_type,
  49.                      0                                                                                AS post_percent,
  50.                      0                                                                                AS repost_percent,
  51.                      0                                                                                AS comment_percent,
  52.                      0                                                                                AS aggregation_type
  53.               FROM mentions
  54.               WHERE {0}
  55.               GROUP BY ROLLUP(clean_source, clean_demographic)
  56.               UNION ALL
  57.               SELECT CleanSource                                                                 AS clean_source,
  58.                      COUNT()                                                                     AS total,
  59.                      CAST('', 'Enum8(\\'\\' = 0, \\'female\\' = 1, \\'male\\' = 2, \\'community\\' = 3)') AS clean_demographic,
  60.                      0                                                                           AS female_percent,
  61.                      0                                                                           AS male_percent,
  62.                      0                                                                           AS community_percent,
  63.                      round(countIf(Sentiment = 'pos') / total * 100)                             AS pos_percent,
  64.                      round(countIf(Sentiment = 'neg') / total * 100)                             AS neg_percent,
  65.                      round(countIf(Sentiment = 'neu') / total * 100)                             AS neu_percent,
  66.                      PostType                                                                    AS post_type,
  67.                      round(countIf(PostType = 'socialmedia') / total * 100)                      AS post_percent,
  68.                      round(countIf(PostType = 'repost') / total * 100)                           AS repost_percent,
  69.                      round(countIf(PostType = 'comment') / total * 100)                          AS comment_percent,
  70.                      1                                                                           AS aggregation_type
  71.               FROM mentions
  72.               WHERE {0}
  73.               GROUP BY ROLLUP(clean_source, post_type)
  74.                  )
  75.         WHERE clean_source != ''
  76.         GROUP BY clean_source
  77.         ORDER BY total DESC
  78.         LIMIT %(offset)s, %(LIMIT)s;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top