Clever Geek Handbook

SQL Olympics：カレンダータスクの解析

こんにちは、Air Radio SQLで！







私たちは地球のIT集団の広範な集団の間でSQL言語を普及させるというトピックを続けています。今回はロシア語の部分です。 しかし、他の惑星の住民も自分を引き上げます。







重力波に同調し、粘液を払いのけ、貝殻を真っ直ぐにして、落ち着きます。始めましょう！..







この記事では、以前書いたSQL Olympicsで与えたカレンダータスクを分析します 。 エキサイティングな再帰と不可解な集約関数、ネストされたクエリ、 武装した データのグループ化-今日、これらすべてが私たちを待っています！







これは分析であり、既成のソリューションではないという事実に注目します。 愚かなコピーと貼り付けを避けるために、私は、少し働いている人だけが完成した結果を得ることができるようにするいくつかの行動を取るつもりです。







第一に、完全で最終的なリクエストコードを提供しません。 最終的なソリューションを得るには、リクエストのすべての部分を1つの全体にインテリジェントに組み立てる必要があります。 人間にとっては、これは難しいことではなく、頭部神経節を使用すれば十分です。 また、自分の代わりにファイルが必要な人による改訂のために、いくつかのかさばるが完全に面白くない部分（月の名前を中央に揃えるなど）を省略します。 したがって、いくつかの改良を正式に行わずに収集された結果は、元の問題の正しい解決策にはなりません。 私の目標は、このようなタスクが原理的にどのように解決されるかを示すことであり、この特定のケースで完成した結果を得ることではないので、これは少し気になりません。







第二に、SQLのOracle方言ではなく、別の方言を取り上げます。 もちろん、些細ではないタスクにはあらゆる種類のグッズが必要です。これらはSQLのバージョンごとにわずかに異なる方法でサポートされており、マトリックスがわずかに失敗すると思われます。 基本的に、 WITH ...



副式をWITH ...



てリクエストの一部を収集できるCTEが必要になり、タスク条件の入力パラメーターはこのように設定されます。 また、以前は未知の長さのシーケンスを生成するために、再帰クエリまたはそれらの類似体が必要になります。最後に、文字列を接着してすべてをまとめる集約関数が必要です。 このようなソフト制限により、SQLサーバーの役割はふりをすることができます コーヒーグラインダー 説明でSQLが省略されているほとんどすべてのもの。 これとPostgreSQL、SQLite、そしてMySQLでさえ最終的にCTEをサポートし始めました。 商用データベースはすべて長い間これを行うことができました。







しばらくheしてから、PostgreSQLを選択して、Oracleデータベースと比較して、このタスクでの外観が少し近くなるように感じました。 SQLの別の方言で必要なすべての解決手順を表現することは問題になりません。私は個人的にこれに迅速に対処しました。 タスクが発生したオリンピアードでは、参照ソリューションが最初に作成されたのはOracle SQLであったことを思い出してください。 まあ、それは私にとってより面白く、一般の人々への娯楽だけではありません。







冗談はさておき、分析を始めましょう。 条件を思い出させてください。







タスク番号1。 カレンダー

ポケットカレンダーを生成する単一のSQLクエリを記述します。 タスクのパラメーターには、カレンダーの年と、月のマトリックスを形成するための行と列の数が示されます。 パラメータは、次のクエリによって設定されます。 







 with param(year, c, r) (…)


ここで、それぞれ、







  • 年-暦年
  • cは、カレンダーマトリックスの列数です。
  • rは、行列の行数です。


月は、カレンダーマトリックスのセルに左から右、上から下の順に配置されます。 各月の数字は、曜日、最初の列の最初の曜日などにあります。 週の初めは、リクエストが起動された時点のデータベースローカリゼーション設定に対応している必要があります。 月の名前もローカライズ設定から取得され、数字の上の中央に表示されます。 隣接する月の数が「互いにくっつかない」ように、月の間にはギャップを残す必要があります。 最初の行は年の中央に行く必要があります。 空行があってはなりません。







たとえば、次のパラメーターを使用します。 







 with param(year, c, r) as (select 2016, 3, 4 from dual)


次のクエリ出力を取得する必要があります。 







  2016    1 2 3 1 2 3 4 5 6 7 1 2 3 4 5 6 4 5 6 7 8 9 10 8 9 10 11 12 13 14 7 8 9 10 11 12 13 11 12 13 14 15 16 17 15 16 17 18 19 20 21 14 15 16 17 18 19 20 18 19 20 21 22 23 24 22 23 24 25 26 27 28 21 22 23 24 25 26 27 25 26 27 28 29 30 31 29 28 29 30 31    1 2 3 1 1 2 3 4 5 4 5 6 7 8 9 10 2 3 4 5 6 7 8 6 7 8 9 10 11 12 11 12 13 14 15 16 17 9 10 11 12 13 14 15 13 14 15 16 17 18 19 18 19 20 21 22 23 24 16 17 18 19 20 21 22 20 21 22 23 24 25 26 25 26 27 28 29 30 23 24 25 26 27 28 29 27 28 29 30 30 31    1 2 3 1 2 3 4 5 6 7 1 2 3 4 4 5 6 7 8 9 10 8 9 10 11 12 13 14 5 6 7 8 9 10 11 11 12 13 14 15 16 17 15 16 17 18 19 20 21 12 13 14 15 16 17 18 18 19 20 21 22 23 24 22 23 24 25 26 27 28 19 20 21 22 23 24 25 25 26 27 28 29 30 31 29 30 31 26 27 28 29 30    1 2 1 2 3 4 5 6 1 2 3 4 3 4 5 6 7 8 9 7 8 9 10 11 12 13 5 6 7 8 9 10 11 10 11 12 13 14 15 16 14 15 16 17 18 19 20 12 13 14 15 16 17 18 17 18 19 20 21 22 23 21 22 23 24 25 26 27 19 20 21 22 23 24 25 24 25 26 27 28 29 30 28 29 30 26 27 28 29 30 31 31


ソリューションアプローチ



このタスクで私が個人的に本当に好きなのは、アルゴリズムの特別な知識やコンピューターサイエンスに固有の何かを必要としないことです。 まあ、つまり、一般的にプログラムできるようにする必要があります。どの再帰などを知っていると便利です。 私の意見では、彼らは今でも学校にいます。 しかし、タスクはいくつかのステップに分けられ、各ステップには独自のニュアンスがあります。 その結果、タスクはそれほど複雑ではないことがわかりましたが、些細なことではありません。







ここで本当に重要なことは、幅と高さがパラメーターで設定されているときにカレンダーマトリックスを生成する方法を想像することです。 高さを使用すると、すべてが非常に単純になります。すべてのSQLダイアレクトは、何らかの方法で、クエリでパラメーターによって指定されたレコード数を生成できます。 通常、これらは再帰的なクエリですが、時には特別な構造体に出くわすこともあります。 たとえば、同じPostgreSQLには、MINからMAXまでの一連の値を生成する特別な generate_series(MIN, MAX)



構造がありました。 次の形式の「クラシック」再帰クエリを使用できます。 







 with recursive seq(n) as ( select MIN union all select n+1 from seq where n<MAX)


しかし、特別なデザインは短くなります。 したがって、適切な行数を取得できます。







次に、パラメーターで指定された列数を生成する方法を決定します。 原則として、すべては上記の行と同じで、必要な数のレコードを生成できます。 そして、それらを出力する必要がある場合、これらのレコードをグループ化して、文字列を操作するための集約関数で接着します。 PostgreQSLでは、これに適したstring_agg()



関数が見つかりました。 







 select string_agg(t::text,'-') from generate_series(MIN,MAX) as s(t);


この手法を使用して、カレンダーの空白マトリックスを生成します。このマトリックスでは、水平および垂直がパラメーターで指定された望ましい月数になります。 毎月、月の各日について、次のように6行7列で親しみのある形で表します。 







 xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx xx


このような各構造を1か月と呼びますので、さらに参照しておくと便利です。 次に、月の列に従って、曜日に応じて番号を配置します。 そして、月の週番号に従って行ごとに-最初の行に最初に入力し、次に2番目の行に入力します。 曜日の数には7つの列があり、6つの行があります。 1か月で6週間を超えることはできません。 もちろん、私は地球グレゴリオ暦を意味します。 他の惑星の住民への理解の要求、このタスクは、主にXXI世紀の地球人のために発明されました（BMの左にある、在職中の013）。







最も重要なことですべてが明らかになったので、残りの技術的な詳細に注意しましょう。 1年のすべての日を生成してから、上記で取得したマトリックスに配置する必要があります。 ここでは、この日数を正しく判断するためにヒッチが必要になる場合があります。 たとえば、年がうるう年である場合を考えます。 または、1582年のこの世のグレゴリオ暦には、10月5日から10月14日までの日がありません（そしてOracleは正直にこれを示しています！）。 したがって、次のように目的の年の日を受け取ります。パラメーターで指定された年の最初の日から、翌年の最初の日までのすべての日を含みます。







そして最後のステップでは、すべてを慎重にまとめ、上から月の名前と年の番号を追加し、空の行を削除する必要があります。







合計の順序：







  1. 年のすべての日を生成します。
  2. 目的の行数と列数で空白行列を生成します。
  3. 適切な場所のカレンダーマトリックスに年の日を入力します。
  4. すべてをまとめて結果を表示します。


実装



行こう







タスクの初期パラメーターは次のとおりです。 







 with params(year, r, c) as (select 2016, 3, 4)


1年のすべての日の生成。 generate_series(START_DATE, END_DATE)



自体はgenerate_series(START_DATE, END_DATE)



を介してgenerate_series(START_DATE, END_DATE)



でき、年の最初の日の始まりと翌年の最初の日からの前日の終わりを示します。 さらに、日付自体に加えて、私たちにとって有用な有用なデータを取得する必要があります：曜日、月、月の日、月の初日の曜日。 必要に応じてこのデータを受け取ることができますが、面倒なのですぐに計算する方が良いでしょう。 PostgreSQL の日付関数のドキュメントを見ると、このためにextract()



関数を使用できることがわかります。 







 ... days(day, moy, dom, dow, fdow) as (select d -- day of year (date) , extract(month from d)::int-1 -- month, 0-11 , extract(day from d)::int -- day of month, 1-31 , extract(isodow from d)::int-1 -- day of week, 0-6 , extract(isodow from date_trunc('month', d))::int-1 -- day of week of first day in month, 0-6 from params p , generate_series( (p.year ||'-01-01')::date , ((p.year+1)||'-01-01')::date - 1, '24:00') as s(d))


次に、列と行に必要な月数を含むカレンダーマトリックスを生成します。 各月の場所（7x6セル）でさらに日付を場所に配置するために、この場所の代わりに使用する月の番号と、その月の日の位置番号をすぐに書き留めます。 除算全体と残差を含む不明確な整数演算がある程度必要になりますが、日付の配置は簡単で便利です。 







 ... matrix(c, r, moy, pos) as (select cc, rr, cc/7 + rr/6*pc, cc%7 + rr%6*7 + 1 from params p , generate_series(0, pc*7-1) as c(c) -- columns , generate_series(0, pr*6-1) as r(r)) -- rows


今、私たちは集まって、彼らの場所で日を整理します。 私が言ったように、予備的な準備を考慮して、これは一度に行われます： 







 ... cal (r,c,dom) as (select r,c,dom from matrix m left outer join days d on d.moy = m.moy -- same month and d.fdow+d.dom = m.pos -- position is day no plus weekday of first day )


典型的なことは、タスクパラメータでカレンダーのサイズが12か月よりも大きいか小さい場合、すべてが正常に機能することです。 月全体が埋められてパーツが空のままになるか、余分な月が収まりませんが、どちらの場合もマトリックスは腐食しません。







すべて、主要部分は完了です。 cal_all



にすべてをきちんとまとめることが残っています。 数字のある月から始めましょう： 







 ... cal_all (no, line) as (-- days in cal matrix select r, string_agg(lpad(coalesce(dom::text,' '), 3+case when c%7=0 then 2 else 0 end) ,'' order by c) from cal group by r ...


ここでは、カレンダーマトリックスの1行にstring_agg()



すべてのものがstring_agg()



関数を使用して1行に接着されています。 この場合、空の日はスペースに置き換えられ、すべての数値は左側にスペースが追加されて調整されます。 さらに、1か月以内に、3つの親密度が毎日、各月（条件c%7=0



）-5の間に割り当てられます。これにより、各月を視覚的に分離できます。 また、行番号を保持しているという事実にも注目します。 最終出力で正しい順序が決定されます。







次に、ここに月の名前を追加します。 これを行うには、すでに作成されているdays



ビューから月の最初の日のみを選択し、行ごとにparams.c



個でグループ化し、 string_agg()



を使用してそれらを接着します。 すべての月がカレンダーマトリックスに配置されていない場合、配置されている月のみの名前を使用します。 月がそれぞれの月の位置より上になるように各名前にスペースを追加することを忘れないでください。また、最終的な並べ替えで月が適切になるように、結果の各行にそのような番号を付けます。 つまり、月を含む最初の行は数字を含む最初の行の前に、2番目は7番目の行の前に表示する必要があります（月に6行を割り当てたことを思い出してください）。 すべて一緒にすると、次のようになります。 







 ... union all -- month names select (moy/pc)*6-0.5, string_agg(lpad(to_char(day, 'Month'), 7*3+2) , '' order by moy) from days d, params p where dom = 1 -- first days of months only and moy < pr*pc group by (moy/pc)


年の真ん中にトップミドルを追加するために残ります： 







 ... union all select -1, lpad(year::text, (7*3+2)*c/2+length(year::text)/2) from params


これで、すべての行を正しい順序でcal_all



から選択し、空の行がある場合は破棄します。 







 ... select line from cal_all where trim(line) <> '' order by no


これがリクエストの最後の部分です。







背後に残っているもの。 params2



表現を作成し、それに「月の各日の慣れ親しみの数」や「月間スペースの数」などの定数を入れてください。 突然それらを変更する必要がある場合は、常に明らかではないコードでこれらの数値を探す必要があり、エラーが発生するためです。 さて、コードをオーバーロードしないように、すべてのアライメント関数を単純化しました。 そして、タスクの状態により、すべてを中央に揃える必要があります。







結論



私が聴衆に伝えようとした主なものと主なもの：そのような問題を解決するのに超複雑なものは何もありません。







このタスクの結果に従って、Oracleと比較したPostgreSQLについて言えること。 機能的には、すべてが対応しており、ほぼ同じとほぼ同じを表現できます。 ある方言ではより便利なものもあれば、別の方言でより便利なものもあります。 機能は異なりますが、そのためにドキュメントが提供されています。 大きな違いはありますか？ はい、あります。 このタスクの例では、少なくとも2つの場所に表示されます。







まず、Oracleはロケールをサポートしており、異なるロケールでは、週は異なる曜日から開始できます。 たとえば、ほとんどのヨーロッパでは、週は月曜日に始まり、米国では日曜日に始まります。 PostgreSQLでは、週の最初の曜日のロケール設定はありません。また、カレンダーを生成して、ユーザーにとってなじみのある日から開始することはできません。







次に、日付の変換とカレンダーの操作のサポートが異なります。 Oracleでは、1582年10月4日はグレゴリオ暦が導入されたときに決定されたように1582年10月15日であり、PostgreSQLには05と1582年10月の残りすべてがあります。 質問はすぐに見えるほど単純ではありません。PostgreSQLのドキュメントには、問題とPostgreSQLでこのように解決される理由を説明する特別なセクションがあります。 しかし、事実は残ります。OracleとPostgreSQLのカレンダーは異なりますが、どちらもグレゴリオ暦であるため、日付を操作するロジックは大幅に異なります。 これは、移植時に重要になる場合があります。







プレゼンテーションが気に入り、SQLについて他に何か書いておくべきアイデアがある場合は、コメントでお待ちしています。 オリンピックの問題の分析の公表を支持する場合、以下の質問に答えることもできます。







本日は、視聴者に別れを告げます。お楽しみに！







More articles:


All Articles