Reporting Functions

• max($field_name) OVER (partition by)
• min() sum() avg() count() 与之类似

• example1

  SELECT monthname(payment_date) payment_month,
         sum(amount) month_total,
         round(sum(amount)/sum(sum(amount)) over () * 100, 2) pct_of_total
  FROM payment
  GROUP BY monthname(payment_date);
  

• 结合MAX() OVER(MIN() OVER)和CASE给最大值和最小值打tag

  SELECT monthname(payment_date) payment_month,
         sum(amount) month_total,
         CASE sum(amount) WHEN max(sum(amount)) over() THEN 'Highest'
             WHEN min(sum(amount)) over() THEN 'Lowest'
             ELSE 'Middle'
         END descriptor
  FROM payment
  GROUP BY monthname(payment_date);
  

Window Frames

• sum() OVER(rows unbounded preceding) 向上累加
• OVER(rows between 1 preceding and 1 following) 对前后两个加本身进行操作
• examples
  • e1 向上计算累计总和

    SELECT yearweek(payment_date) payment_week,
           sum(amount) week_total,
           sum(sum(amount)) over (order by yearweek(payment_date)
               rows unbounded preceding) rolling_sum
    FROM payment
    GROUP BY yearweek(payment_date)
    ORDER BY 1;
    

  • e2 计算近三周销售额的均值

    SELECT yearweek(payment_date) payment_week,
           sum(amount) week_total,
           avg(sum(amount)) OVER (ORDER BY yearweek(payment_date)
               rows between 1 preceding and 1 following)
    FROM payment
    GROUP BY yearweek(payment_date)
    ORDER BY 1;
    

• avg(amount) OVER (range between interval 3 day preceding AND interval 3 following) 计算前后一周内销售额的均值，强调间隔内，因为每行之间的数值可能存在间隔，如(1,3,4,5,10)，若想明确限定期限，可采用range BETWEEN $interval preceding AND $interval following

• exmple

  SELECT date (payment_date), sum(amount),
         AVG(sum(amount)) OVER (order by date(payment_date)
             RANGE BETWEEN interval 3 day preceding
             AND interval 3 day following)
  FROM payment
  WHERE payment_date BETWEEN '2005-07-01' AND '2005-09-01'
  GROUP BY date(payment_date)
  ORDER BY 1;
  

Lag and Lead

• lag 从某列中提取数据，但是会向下移一行
• lead 从某列中提取数据，但是会向上移动一行
• exmaple

  SELECT yearweek(payment_date) payment_week,
         sum(amount) week_total,
         lag(sum(amount),1)
          OVER (ORDER BY yearweek(payment_date)) prev_wk_tot,
         lead(sum(amount),1)
          OVER (ORDER BY yearweek(payment_date)) next_wk_tot
  FROM payment
  GROUP BY yearweek(payment_date)
  ORDER BY 1;
  

  • 以下两者相同
    • lag(sum(amount),1) OVER (ORDER BY yearweek(payment_date) desc)
    • lead(sum(amount),1) OVER (ORDER BY yearweek(payment_date))

Column Value Concatenation

• group_concat($tbl.fld ORDER BY $tbl.fld SEPARATOR '$separator')
• example

  SELECT f.title,
         group_concat(a.last_name ORDER by a.last_name SEPARATOR ', ') actors
  FROM actor a
  INNER JOIN film_actor fa on a.actor_id = fa.actor_id
  INNER JOIN film f on fa.film_id = f.film_id
  GROUP BY f.title
  HAVING count(*)  = 3;