615. Average Salary: Departments VS Company （問題レベル: Hard）要課金
今日の問題はこれ。
会社の月別部門ごとの売上を全体平均と比較した評価をリストアップするという問題。
まだまだ、GROUP BYで一緒に扱えない変数の処理などに戸惑ったりして頭の中がスッキリせずSQLの達人には程遠いです。
しかし、１か月半も毎日課題に取り組んでいると流石に成長を感じます。
Hardレベルの問題もちょっとしたコマンドの検索くらいで自力で記載できるようになってきました。
WITH句は使い慣れると便利。頭がスッキリしないときにとりあえず理想的な中間テーブルを作って置ける。
ただし、実行速度的には注意が必要なのかもです。
今回はWITH句を３つも使い、実行速度は遅いですが、理解はしやすい回答だと思います。
最終的に提出した答えはこれ。
SQL
WITH tb1 AS (
SELECT FORMAT(s.pay_date, 'yyyy-MM') AS pay_month,
e.department_id, s.amount
FROM salary s
JOIN employee e
ON s.employee_id=e.employee_id
),
tb2 AS (
SELECT department_id, AVG(amount*1.0) AS mean, pay_month
FROM tb1
GROUP BY department_id, pay_month
),
tb3 AS (
SELECT AVG(amount*1.0) AS mean, pay_month
FROM tb1
GROUP BY pay_month)
SELECT a.pay_month, b.department_id,
CASE WHEN b.mean > a.mean THEN 'higher'
WHEN b.mean = a.mean THEN 'same'
ELSE 'lower' END AS comparison
FROM tb3 a
JOIN tb2 b
ON a.pay_month=b.pay_month
今日のポイントは日付周りの関数。
最初、日付は文字列扱いかと思いLEFT(), SUBSTRING()を使ったがエラー。
ポイントは日付情報のフォーマットはFORMAT(日付変数,'書式')を使う。
| 書式 | 説明 | 出力結果例 |
|---|---|---|
| yyyy | ４桁の西暦 | 2022 |
| yy | 西暦下２桁 | 22 |
| MM | 月(ゼロ埋め) | 03 |
| M | 月 | 3 |
| dd | 日(ゼロ埋め) | 02 |
| dd | 日 | 2 |
| yyyy-MM-dd | 上記の組み合わせ例１ | 2022-03-20 |
| yyyy/M/d | 上記の組み合わせ例２ | 2022/3/2 |
| HH | ２桁の時間（２４時間表示0埋め） | 15 |
| hh | ２桁の時間（12時間表示） | 3 |
| tt | PM/AM表示 | PM |
| mm | 分(ゼロ埋め) | 09 |
| ss | 秒(ゼロ埋め) | 03 |
| dddd | 曜日 | Sunday |
| ddd | 曜日省略 | Sun |
日付の表示方法で覚えておきたいのはもう一つ
CONVERT(VARCHAR,日付変数,数値でのフォーマット指定))
数値でのフォーマット指定には色々ありますが、下記を覚えておけば良いでしょう。
| 書式 | 出力結果例 |
|---|---|
| 111 | 2022/03/02 |
| 112 | 20220302 |
| 23 | 2022-03-02 |
| 8 | 23:09:03 |
-
LeetCodeの問題は、
MS SQL Serverで解いています。