第３章：集約

3-1 データ数、種類数の算出

• カウントとユニークカウント
• SQL
• 
  SELECT
      hotel_id,
      -- nullでない行数のカウント
  　COUNT(reserve_id) AS rsv_cnt,
      -- 重複除いた数
      COUNT(distinct customer_id) AS cus_cnt
  FROM
      work.reserve_tb
  GROUP BY
      hotel_id;
• Python
• 
  # agg関数で、集約処理をまとめて指定
  # reserve_idにcount関数
  # customer_idにnunique関数
  result = reserve_tb.groupby('hotel_id') \
  　　　　　　　　　.agg({'reserve_id': 'count', 'customer_id': 'nunique'}
  
  result.reset_index(inplace=True)
  result.columns = ['hotel_id', 'rsv_cnt', 'cus_cnt']

3-2　合計値の算出

• SQL
• 
  SELECT
      hotel_id,
      people_num,
      SUM(total_price) AS price_sum
  FROM
      work.reserve_tb
  GROUP BY
      hotel_id, people_num;
• Python
• 
  result = reserve_tb.groupby(['hotel_id', 'people_num'])['total_price'] \
  　　　　　　　　  .sum().reset_index()
  
  result.rename(columns={'total_price': 'price_sum'}, inplace=True)

3-3 極値、代表値の算出

　データ数が膨大な時は、中央値やパーセンタイル値を計算することは困難になることがある。

• 代表値（最大値、最小値、平均値、中央値、%tile値など）
• SQL
• 
  SELECT
      hotel_id,
      -- 最大値
  　MAX(total_price) AS price_max,
      -- 最小値
  　MIN(total_price) AS price_min,
      -- 平均値
      AVG(total_price) AS price_avg,
      -- 中央値
      MEDIAN(total_price) AS price_mid,
      -- 20パーセンタイル値算出
      PERCENTILE_CONT(0.2) WITHIN GROUP(ORDER BY total_price)
                                                                                          AS price_20per
  FROM
      work.reserve_tb
  GROUP BY
      hotel_id;

• 構文
  

  PERCENTILE_CONT( numeric_literal )

  WITHIN GROUP( ORDER BY order_by_expression [ ASC | DESC ] )
  -- numeric_literal : 計算する百分位数です。 値は 0.0 ～ 1.0
  -- order_by_expression : パーセンタイルの対象列

• Python
• 
  result = reserve_tb.groupby('hotel_id') \
               .agg({'total_price': ['max', 'min', 'mean', 'median',
                                             lambda x: np.percentile(x, q=20)]}) \
               .reset_index()
  
  reset_columns = ['hotel_id', 'price_max', 'price_min', 'price_mean',
                              'price_median', 'price_20per']

3-4 ばらつき具合の算出（分散と標準偏差）

• SQL
• 
  SELECT
  　　hotel_id,
  　　-- VARIANCE関数:分散。COALESCE関数で分散値NULLのとき0に変換
  　　COALESCE(VARIANCE(total_price), 0) AS price_var,
  　　-- データ数が2件以上の時、STTDEV関数で標準偏差を求める
  　　COALESCE(STDDEV(total_price), 0) AS price_std
  FROM
  　　work.reserve_tb
  GROUP BY
  　　hotei_id;

　　　　COALESCE関数により、データ数が1件のときに分散と標準偏差に0を入れている。

• Python
• 
  result = reserve_tb.groupby('hotel_id') \
  　　　　.agg({'total_price': ['var', 'std']}.rest_index()
  
  # データ1件の時、分散と標準偏差がnaになっているので、0に置き換える
  result.fillna(0, inplace=True)

3-5 最頻値の算出

例）ホテルの予約レコードで、予約テーブルの予約金額を1000単位にカテゴリ化して最頻値を算出する。

• SQL
• 
  SELECT
  　ROUND(total_price, -3) AS total_price_rounde
  FROM
  　work.reserve_tb
  GROUP BY
  　total_price_round
  -- COUNT関数で算出した金額別の予約数を大きい順に並び替え
  ORDER BY COUNT(*) DESC
  -- LIMIT句で最初の1件のみ結果を取得
  LIMIT 1;
• Python
• 
  reserve_bt['total_price'].round(-3).mode()

3-6 順位の算出

例１）時系列に番号を付与

ホテルの予約レコードにおいて、顧客ごとに予約日時の順位を古い順に付ける。同じ予約日時の場合は、データ行の読み込み順に小さな順位を付ける。

• SQL 
• 
  SELECT
  　*,
  　-- ROW_NUMBERで順位を取得
  　-- PARTITION BY customer_idで顧客ごとに順位を取得するように設定
  　-- ORDER BY reserve_datetimeで順位を予約日時の古い順に設定
  　ROW_NUMBER() OVER(
  　　　　　　　　　　　　　PARTITION BY customer_id
  　　　　　　　　　　　　　ORDER BY  reserve_datetime
  　　　　　　　　　　　　　) AS log_no
  FROM
  　work.reserve_tb;
•  Python
  
• 
  # rank関数で並び替えるため、データ型を文字列からtimestamp型に変換
  reserve_tb['reserve_datetime'] = pd.to_datetime(reserve_tb['reserve_datetime'],
  　　　　　　　　　　　　　　　　　　　format = '%Y-%m-%d %H:%M:%S')
  #顧客ごとにまとめたreserve_datetimeを生成し、rank関数により順位を生成
  reserve_tb['log_no'] = reserve_tb.groupby('customer_id')['reserve_datetime'] \
  　　　　　　　　　　　　　　　.rank(ascending=True, method='first')

例２）ホテルの予約レコードにおいて、ホテルごとの予約数に順位付けをする。同じ予約数の場合は、同予約数の全ホテルに最小の順位を付ける。

• SQL
• 
  SELECT
  　hotel_id,
  　-- RANK関数で予約数の順位を指定
  　-- COUNT(*) をRANKの基準として指定（集約したあとの予約数に対して順位を付ける算出処理）
  　RANK () OVER( ORDER BY COUNT(*) DESC) AS rsv_cnt_rank
  FROM
  　 work.reserve_tb
  -- hotel_idを集約単位に指定、予約数を計算するための集約指定でRANK関数には影響がない
  GROUP BY
  　hotel_id;
• Python
• 
  # 予約回数を計算
  rsv_cnt_tb = reserve_tb.groupby('hotel_id').size().reset_index()
  rsv_cnt_tb.columns = ['hotel_id', 'rsv_cnt']
  
  # 予約回数をもとに順位を計算
  # methodをminに指定し、同じ値の場合は取り得る最小順位に指定
  rsv_cnt_tb['rsb_cnt_rank'] = \
  　　　　　　　　　　rsv_cnt_tb['rsv_cnt'].rank(asecending=False,method='min')
  
  # 必要のないrsv_cnt列を削除
  rsv_cnt_tb.drop('rsv_cnt', axis=1, inplace=True)