SQLアンチパターンのジェイウォークを縦持ちテーブルに変換する

PostgreSQL11 TIPS SQL

概要

SQLアンチパターンの1つであるジェイウォークと遭遇して、
一回のSQLで縦持ちのテーブルに変換できたのでその方法のメモです。

前提条件

テーブル

元テーブル

下記のようなテーブルがあります。

id value
1 apple,orange,banana
2 apple
3 orange,banana

valueにはカンマ区切りで値が入っています。
テーブル名は「jaywalk_table」とします。

取得したい形

id branch_number splited_value
1 1 apple
1 2 orange
1 3 banana
2 1 apple
3 1 orange
3 2 banana

カンマ区切りの値に対してそれぞれを1レコードとして枝番号をつけます。

SQL

実行したSQLは下記です。

SELECT
    id,
    ROW_NUMBER() OVER(PARTITION BY id) AS branch_number,
    splited_value
FROM
(
    SELECT
        id,
        regexp_split_to_table(value,',') AS splited_value
    FROM
        jaywalk_table
) AS temp

解説

regexp_split_to_table

regexp_split_to_table関数は指定した区切り文字で区切って、区切られた値をそれぞれレコードに変換します。
例えば、下記のSQLを実行すると・・・

SELECT regexp_split_to_table('apple,orange,banana', ',')

下記のような結果が返ってきます。

regexp_split_to_table
apple
orange
banana

この関数を使えばジェイウォークは解消されます。

ROW_NUMBER

ROW_NUMBER関数はWindow関数の一つです。
Window関数はグループごとに計算を行い、計算された結果を各レコードに返します。

今回でいくとOVER(PARTITION BY id)はidごとにグルーピングすることを表し、
ROW_NUMBER()でそのグルーピング内で行数を返してねという感じの流れです。

まとめ

集合を返す関数とWindow関数を組み合わせると良い感じにジェイウォークを解消できますね。

失敗から学ぶ RDBの正しい歩き方を読み終わりました

失敗から学ぶ RDBの正しい歩き方を一通り読み終えたので書評です。

失敗から学ぶ RDBの正しい歩き方：書籍案内｜技術評論社

評価

★★★★★★★★☆☆（8/10）
評価の目安はこちら

オススメできる人

  • RDBを使ったシステムの運用をしている人

オススメできない人

正規化、PK/FK、インデックスなど意味や仕組みを知っていることが前提条件になっていると思います。
書籍内で説明もしてくれているので読む分には問題ないかもしれないですが、
しっかりと理解する上では前提になっていると思います。

感想

書籍全体としては現場で遭遇するアンチパターンとそれに対するノウハウといった内容でした。

1-9章まではテーブル設計に関する基本という内容でした。
私も今となっては当たり前の内容だなと感じることはできますが、
サービスの運用をしたことなかった時期にここまで考えられていなかったような気がします。

10-20章は運用周りの基本といった内容でした。
テーブル設計だけだと運用周りまで含めた問題の回避にはならず、
10-20章で挙げられているような問題が発生してしまうと思います。

全体的に「運用する上では当たり前に考えないといけないことだよねー」という内容です。
ただ、実際の現場ではできていないことも多くあります。
だいたいは実体験で覚えることが多いと思いますが、この書籍は実体験で覚えることを明文化されています。

RDBを運用する上での必読本と言えると思います。

Software Architecture in Practice, Third Editionを読み終わりました

書評

Software Architecture in Practice, Third Editionを一通り読み終えたので書評です。

Software Architecture in Practice, Third Edition

評価

★★★★★★★★☆☆（8/10）
評価の目安はこちら

オススメできる人

  • ソフトウェアアーキテクトを目指す人、または実務経験者

オススメできない人

強いてあげるならタイトルがそのもなのでアーキテクチャ周りに興味ない人。

感想

Intoroductionにもある通りアーキテクチャとは何か？なぜ重要なのか？
そして、何を知っておくべきでどういうことをしないといけないかが書かれています。
私自身が英語をしっかりと読めるわけではないので、理解できていないところは多いのですがまとめるとそんなところだと思います。　　

Part TwoではQuality Attributes（品質属性）でどんなものがあるかとそれぞれに対する戦略が説明されています。
Availability/Performance/Security/Testabilityあたりは一般的にもよく聞く内容ですが
Interoperability（相互運用性）/Modifiability（変更容易性）あたりは品質属性と言われれば確かにと思う内容でした。
Usabilityに関してはアーキテクチャとどういう関わりがあるかというのが疑問でしたが
キャンセルできるとか一時停止できるとかというところに関わってくるとあってなるほどと思いました。

Part Threeではアーキテクチャのライフサイクルに関連する内容が書かれています。
実際にアーキテクチャをどのように評価すればいいのかというのは気になっていたので
Architecture Evaluationの章はとても勉強になりました。

Part Fourではアーキテクチャのビジネス的な側面についてが書かれています。
この辺は個人的に理解が浅い部分ではありますが、コスト/利益や組織的な話が書かれていると思います。

Part FiveはクラウドやOSS関連がアーキテクチャにどのように影響しているのかという話でした。

全部を理解できたわけではないですが、アーキテクチャの全般的な内容が書かれているなと感じました。
困った時の道しるべとしても使えそうな気がします。

入門 監視を読み終わりました

書評

入門 監視を一通り読み終えたので書評です。

O'Reilly Japan - 入門 監視

評価

★★★★★★★★☆☆（8/10）
評価の目安はこちら

オススメできる人

  • Webサービスの運用をしている人
  • 監視について全体像を知りたい人

オススメできない人

  • Zabbixなど特定のツールの使い方を知りたい人

感想

今まで本だけで学んできて実際に監視の設定を行ったことがないので、
「監視ツールの使い方はわかったけど何を監視すればいいのか？」という疑問に抱いていました。
Webアクセスとったり、OSのメトリクスをとればいいくらいの感覚でした。

そんな疑問を持っている私にはとっては色々と学ぶべきことが多かった本でした。
特に「5章　ビジネスを監視する」に関しては今まで考えたことない観点での監視だったので
目から鱗が落ちたという気持ちになりました。

SaaSの話があったり、フロントエンドの監視についての話があったりと
現世代的な監視のあり方というのも学べると思います。

個人的にはWebサービスを運用をしている人は必読かと思います。

DNSがよくわかる教科書を読み終わりました

書評

DNSがよくわかる教科書を一通り読み終えたので書評です。

SBクリエイティブ:DNSがよくわかる教科書

評価

★★★★★★★☆☆☆（7/10）
評価の目安はこちら

オススメできる人

  • DNSの基本をおさえたい人

オススメできない人

  • BINDなど特定のライブラリの使い方を知りたい人

強いて挙げるとしたら、特定のライブラリの使い方を知りたい人かと思います。

感想

この本の目次などはこちらを参照するのが良いと思います。

基礎編ではDNSの歴史的な系から始まり、どういう役割を持ったサーバがあるかといったようなことが書かれています。
スタブリゾルバ、フルサービスリゾルバ、権威サーバといった役割が説明されているのがよかったと感じました。

実践編ではDNSを使って名前解決して運用するところまでが説明されています。
私個人としてはAレコードやNSレコードの知識が曖昧だったのですが、そこがわかりやすく説明されていると感じました。

アドバンス編ではセキュリティや引っ越しなど長期の運用を行った時に遭遇するイベントの対応について書かれています。

個人的にはDNSの基本的な部分が網羅されている本だと思います。
私はプログラマなので運用周りはやる予定はないのですが、
DNSの情報を管理する人でなくても読む価値はある本だと思います。

現場至上主義 Spring Boot2徹底活用を読み終わりました

書評

現場至上主義 Spring Boot2徹底活用を一通り読み終えたので書評です。

現場至上主義　Spring Boot2徹底活用（廣末 丈士／宮林 岳洋 著、高安 厚思 監修） | 書籍 本 | ソシム

評価

★★★★★★☆☆☆☆（6/10）
評価の目安はこちら

オススメできる人

  • Spring Bootを使って開発したい人

初心者本の次に読むと良いと思う本です。

オススメできない人

  • Spring Boot初心者

Spring Bootの基本的な使い方については説明されていないので初心者の人にはオススメできません。

感想

Spring Bootを使ってサービスを開発するための準備や
チームでの開発を効率化するためのツールや設定、運用するための設定など
サービスを開発するために必要なことが詰まっていると思います。

色々な設定ができるSpring Bootですがコードが書かれている部分が多いため、
どのように設定すればいいかというのは参照し易いと思います。

個人的な感想としては細かいコードよりは設計のTIPS的なものが欲しいなとは思いました。
それでもコードが書かれている部分が多いので
「あれ？あの設定ってどうやるんだっけ？」といったシチュエーションでリファレンス的に使えそうです。

Spring Boot初心者が初心者本の次に読む本として、
困った時のリファレンスとしてオススメだと思います。

Elasticsearch実践ガイドを読み終わりました

書評

Elasticsearch実践ガイドを一通り読み終えたので書評です。

Elasticsearch実践ガイド - インプレスブックス

評価

★★★★★★☆☆☆☆（6/10）
評価の目安はこちら

オススメできる人

  • Elasticsearch初心者

オススメできない人

  • Elastic Stackについて学びたい人

タイトルの通りElasticsearch単体にフォーカスを合わせているので
Elastic Stackについて学びたい人にとってはあまりオススメしません。

感想

  • Elasticsearch内で使われている用語の説明
  • 基本的なCRUDの使い方
  • クラスタがどのような構成で動いているのか
  • 内部ではどのようにインデックスされているのか

といったElasticsearchの基礎知識が書かれています。
後半の章では監視やバックアップ/リストアなどの運用に必要なことも書かれています。

動かすための基礎知識と運用するための基礎知識が書かれているので、
これ一冊を読んでおけば運用するにあたって必要な知識が得られるのではないかと思います。