RETURNING id as id

-- part1
SELECT
  -- timestamptzに変換
  TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS')
    AS reserve_datetime_timestamptz,

  -- timestamptzに変換後に、timestampに変換
  CAST(
    TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP
  ) AS reserve_datetime_timestamp,

  -- 日付と時刻の文字結合してから、TIMESTAMPに変換
  TO_TIMESTAMP(checkin_date || checkin_time, 'YYYY-MM-DDHH24:MI:SS')
    AS checkin_timestamptz,

  -- 日時文字列を日付型に変換（時刻情報は変換後削除されている）
  TO_DATE(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS') AS reserve_date,

  -- 日付文字列を日付型に変換
  TO_DATE(checkin_date, 'YYYY-MM-DD') AS checkin_date

FROM work.reserve_tb


-- part2
WITH tmp_log AS(
	SELECT
		CAST(
      TO_TIMESTAMP(reserve_datetime, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP
    ) AS reserve_datetime_timestamp,
	FROM work.reserve_tb
)
SELECT
	-- DATE型もDATE_PART関数は利用可
	-- TIMESTAMPTZ型はDATE_PART関数は利用不可
	-- 年を取得
	DATE_PART(year, reserve_datetime_timestamp)
	  AS reserve_datetime_year,

  -- 月を取得
	DATE_PART(month, reserve_datetime_timestamp)
	  AS reserve_datetime_month,

  -- 日を取得
	DATE_PART(day, reserve_datetime_timestamp)
	  AS reserve_datetime_day,

  -- 曜日(0 は日曜日、1＝月曜日)を取得
	DATE_PART(dow, reserve_datetime_timestamp)
	  AS reserve_datetime_day,

  -- 時刻の時を取得
	DATE_PART(hour, reserve_datetime_timestamp)
	  AS reserve_datetime_hour,

  -- 時刻の分を取得
	DATE_PART(minute, reserve_datetime_timestamp)
	  AS reserve_datetime_minute,

  -- 時刻の秒を取得
	DATE_PART(second, reserve_datetime_timestamp)
	  AS reserve_datetime_second,

  -- 指定したフォーマットの文字列に変換
	TO_CHAR(reserve_datetime_timestamp, 'YYYY-MM-DD HH24:MI:SS')
	  AS reserve_datetime_char

FROM tmp_log

-- small_area_nameごとにホテル数をカウント、結合キーを判定するためのテーブル
WITH small_area_mst AS(
  SELECT
    small_area_name,

    -- 20件以上であればjoin_area_idをsmall_area_nameとして設定
    -- 20件未満であればjoin_area_idをbig_area_nameとして設定
    -- -1は、自ホテルを引いている
    CASE WHEN COUNT(hotel_id)-1 >= 20
			THEN small_area_name ELSE big_area_name END AS join_area_id

  FROM work.hotel_tb
  GROUP BY big_area_name, small_area_name
)
-- recommend_hotel_mstはレコメンド候補のためのテーブル
, recommend_hotel_mst AS(
  -- join_area_idをbig_area_nameとしたレコメンド候補マスタ
  SELECT
    big_area_name AS join_area_id,
    hotel_id AS rec_hotel_id
  FROM work.hotel_tb

  -- unionで、テーブル同士を連結
  UNION

  -- join_area_idをsmall_area_nameとしたレコメンド候補マスタ
  SELECT
    small_area_name AS join_area_id,
    hotel_id AS rec_hotel_id
  FROM work.hotel_tb
)
SELECT
  hotels.hotel_id,
  r_hotel_mst.rec_hotel_id

-- レコメンド元のhotel_tbを読み込み
FROM work.hotel_tb hotels

-- 各ホテルのレコメンド候補の対象エリアを判断するためにsmall_area_mstを結合
INNER JOIN small_area_mst s_area_mst
  ON hotels.small_area_name = s_area_mst.small_area_name

-- 対象エリアのレコメンド候補を結合する
INNER JOIN recommend_hotel_mst r_hotel_mst
  ON s_area_mst.join_area_id = r_hotel_mst.join_area_id

  -- レコメンド候補から自分ホテルを除く
  AND hotels.hotel_id != r_hotel_mst.rec_hotel_id

-- awesomeなsqlの処理はこれを参考にしてみる
-- https://github.com/ghmagazine/awesomebook/tree/master/preprocess


-- part1: VARIANCE関数にtotal_priceを指定し、分散値を算出
-- * COALESCE関数によって、分散値がNULLのときは0に変換
-- * データ数が2件以上の場合は、STDDEV関数にtotal_priceを指定し、標準偏差値を算出
SELECT
  hotel_id,
  COALESCE(VARIANCE(total_price), 0) AS price_var,
  COALESCE(STDDEV(total_price), 0) AS price_std
FROM work.reserve_tb
GROUP BY hotel_id


-- part2
SELECT
  ROUND(total_price, -3) AS total_price_round
FROM work.reserve_tb
GROUP BY total_price_round
ORDER BY COUNT(*) DESC
LIMIT 1


-- part3: ランキング
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

-- まとめて集約
SELECT
  hoge_id,
  COUNT(reserve_id) AS rsv_cnt,
  COUNT(distinct customer_id) AS cus_cnt
FROM work.reserve_tb
GROUP BY hoge_id

SELECT *
FROM hoge

-- hoge_dateが、2016-10-12から2016-10-13までのデータに絞り込み
-- WHERE hoge_date BETWEEN '2016-10-12' AND '2016-10-13'

-- サンプリング: 乱数を生成し、0.5以下のデータ行のみ絞り込み
-- WHERE RANDOM() <= 0.5


-- WITH句によって、一時テーブルreserve_tb_randomを生成
WITH hoge_random AS(
  SELECT
    *,

    -- customer_idに対して一意の値となる乱数の生成
    -- 生成した乱数をcustomer_idごとにまとめて、1番目の値を取り出す
    FIRST_VALUE(RANDOM()) OVER (PARTITION BY customer_id) AS random_num

  FROM hoge
)
-- *ですべての列を抽出しているが、random_numを外したい場合は列を指定する必要あり
SELECT *
FROM hoge_random

-- 50%サンプリング、customer_idごとに設定された乱数が0.5以下の場合に抽出
WHERE random_num <= 0.5

DROP TABLE IF EXISTS SupportEvent;  
GO

CREATE TABLE SupportEvent  
(  
SupportEventId  int not null   identity(1,1)  
PRIMARY KEY NONCLUSTERED,  
StartDateTime        datetime2     not null,  
CustomerName         nvarchar(16)  not null,  
SupportEngineerName  nvarchar(16)      null,  
...
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA\_AND_DATA);  
GO  

ALTER TABLE SupportEvent  
  ADD CONSTRAINT constraintUnique_SDT_CN  
  UNIQUE NONCLUSTERED 
      (StartDateTime DESC, CustomerName);  
GO

ALTER TABLE SupportEvent  
  ADD INDEX idx_hash_SupportEngineerName  
  HASH (SupportEngineerName) 
      WITH (BUCKET_COUNT = 64);
GO

IF EXISTS (SELECT name FROM sys.indexes  
    WHERE name = N'index_name'  
    AND object_id = OBJECT_ID (N'schema.tableName'))      
DROP INDEX index_name
    ON schema.tableName  
GO  

CREATE NONCLUSTERED INDEX index_name  
    ON schema.tableName (col1, col2)  
    WHERE <filter-condition>
GO  

IF EXISTS (SELECT name from sys.indexes  
           WHERE name = N'index_name')   
   DROP INDEX index_name ON schema.tableName;   
GO  

CREATE UNIQUE INDEX index_name   
   ON schema.tableName (colName);   
GO  

GO  
-- Find an existing index named index_name
-- and delete it if found.   
IF EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'index_name')   
    DROP index_name ON schema.tableName;   
GO  

CREATE NONCLUSTERED INDEX
    index_name ON schema.tableName (ColName);   
GO