Блог

Я пытаюсь вычислить сумму числового столбца «Потребление» на основе определенного дня и недели. Я уже успешно создал числовое значение для «Потребления» за один день на текущей неделе и в дополнение к одному дню с предыдущей недели. Однако я не могу создать столбец, который суммирует «Потребление» на основе определенных дней недели.

Я пробовал группировать по неделям, группировать по дням и использовать оператор SUM(CASE WHEN THEN END), но не смог получить сумму потребления за несколько дней

 SELECT  final.master_track_id,
  final.artist,
  final.track,
  final.global_week,
  final.dayofglobalweek,
  final.transaction_date,
  final.consumption,
  final.consumption AS previous_consumption,
  final.TW_Three_Day_Trend, 
  final.LW_Three_Day_Trend,
  (final.TW_Three_Day_Trend - final.LW_Three_Day_Trend) / NULLIF(final.LW_Three_Day_Trend,0) AS Final_Delta


FROM (
#Select your columns here
SELECT DISTINCT
  cur.master_track_id,
  cur.artist,
  cur.track,
  cur.global_week,
  cur.dayofglobalweek,
  cur.transaction_date,
  cur.consumption,
  prev.consumption AS previous_consumption,
  #The total change in consumption (TW - LW) / LW
  ((cur.consumption - prev.consumption) / prev.consumption) * 100.0 AS PER_DAY_DELTA,
  #First case statement that creates category for the TW_Three_Day_Trend
  SUM(CASE 
    WHEN cur.dayofglobalweek IN (6,7,1) THEN cur.consumption ELSE 0
  END) AS TW_Three_Day_Trend,
  #Second case statement that creates category for the LW_Three_Day_Trend
  SUM(CASE 
    WHEN prev.dayofglobalweek IN (6,7,1) THEN prev.consumption ELSE 0
  END) AS LW_Three_Day_Trend




#create the places from which you'll pull your various columns from. This will involve creating new tables
FROM (

  SELECT
    chae.master_track_id,
    chae.artist,
    chae.track,
    t1.transaction_date,
    t2.dayofglobalweek,
    t2.global_week,
    SUM(t1.consumption) AS consumption

#create the first set of transactions table
  FROM (
    SELECT
      isrc,
      transaction_date,
      SUM(track_adjusted_units) AS consumption
    FROM
      `umg-marketing.consumption.transactions` 
    WHERE
      transaction_date >= '2019-02-15'
      AND transaction_date <= '2019-04-15'
      AND sales_country_code = "US"
      and sales_currency_code = "USD"
      AND subject_area IN ("Streams Sales", "Digital Sales")
      AND usage_type NOT IN ("Play / Stream (Webcasting)")
      AND product_type IN ("Track")
      AND transaction_type = "Sale"
    GROUP BY
      isrc,
      transaction_date ) t1

  #join first set of tables together
  #create the first instance of the product table

  LEFT JOIN (
    SELECT
      master_track_id,
      master_artist,
      master_track,
      isrc
    FROM
      `umg-marketing.metadata.product` ) p
  ON 
    p.isrc = t1.isrc

  #join the day table
  #create the first instance of the day table
  LEFT JOIN (
    SELECT
      day,
      global_week,
      dayofglobalweek
    FROM
      `umg-marketing.metadata.day` ) t2
  ON
    t1.transaction_date = t2.day

    #join the dchae table
    #create the first instance of the chae table
    LEFT JOIN (
    SELECT
      master_track_id,
      artist,
      track
    FROM
      `umg-user.chaed.test_FTL` ) chae

    ON 
      chae.master_track_id = p.master_track_id

  #Group by statement that brings the entire first instance together   
  GROUP BY
    chae.master_track_id,
    chae.artist,
    chae.track,
    t1.transaction_date,
    t2.global_week,
    t2.dayofglobalweek

   #end of "cur" instance
  ) cur

#now you join the second instance of the tables labeled as "prev"  
LEFT JOIN (
  #pulling the second set of columns to pull from labeled as "prev"
  SELECT
    chae.master_track_id,
    chae.artist,
    chae.track,
    t2.dayofglobalweek,
    t2.global_week,
    SUM(t1.consumption) AS consumption

  FROM (

    #create the second instance of transactions table

    SELECT
      isrc,
      transaction_date,
      SUM(track_adjusted_units) AS consumption
    FROM
      `umg-marketing.consumption.transactions` 
    WHERE
      transaction_date >= '2019-02-15'
      AND transaction_date <= '2019-04-15'
      AND sales_country_code = "US"
      and sales_currency_code = "USD"
      AND subject_area IN ("Streams Sales", "Digital Sales")
      AND usage_type NOT IN ("Play / Stream (Webcasting)")
      AND product_type IN ("Track")
      AND transaction_type = "Sale"
    GROUP BY
      isrc,
      transaction_date ) t1
  LEFT JOIN (
    #create the second instance of product table
    SELECT
      master_track_id,
      master_artist,
      master_track,
      isrc
    FROM
      `umg-marketing.metadata.product` ) p
  ON 
    p.isrc = t1.isrc

 LEFT JOIN (
    #create the second instance of day table
    SELECT
      day,
      dayofglobalweek,
      global_week
    FROM
      `umg-marketing.metadata.day` ) t2
  ON
    t1.transaction_date = t2.day

  LEFT JOIN (
    #create the second instance of chae table
    SELECT
      master_track_id,
      artist,
      track
    FROM
      `umg-user.chaed.test_FTL` ) chae

    ON 
      chae.master_track_id = p.master_track_id

  #Group by statement for the prev 

  GROUP BY
    chae.master_track_id,
    chae.artist,
    chae.track,
    t2.dayofglobalweek,
    t2.global_week

  ) prev

 #most outermost join on these criteria 
ON
  cur.master_track_id = prev.master_track_id
  AND cur.global_week - 1 = prev.global_week
  AND cur.dayofglobalweek = prev.dayofglobalweek

#outermost Group By statement for the entire query
GROUP BY
  cur.master_track_id,
  cur.artist,
  cur.track,
  cur.global_week,
  cur.dayofglobalweek,
  cur.transaction_date,
  cur.consumption,
  prev.consumption,
  prev.dayofglobalweek
 ) final
 WHERE master_track_id IS NOT NULL
 AND artist IS NOT NULL
 AND track IS NOT NULL
 ORDER BY global_week, dayofglobalweek, final.consumption
 

Я ожидаю, что потребление за каждый день будет суммой нескольких дней (примерно в 3 раза больше), но значение TW / LW_Three_Day_Trend будет таким же, как и единственное число day.output