SQL
PIVOT または UNPIVOT を使わなくてはならない状況は、 データ設計に問題があると思う。 よってどうしても不慣れであった。 Oracle のPIVOTは、以下の書式で書くことになっていて PIVOT(集約関数 FOR 集約条件列 IN(集約条件値1 as 集約後列名1, 集約条件値2…
Oracle DBMS_RANDOM.VALUE は、０から１の数を生成し、精度は38桁 SELECT DBMS_RANDOM.VALUE FROM dual は、0.73988067528604570993642928024798559837 を生成したりする。整数 1～1000 の範囲で欲しい時は SELECT TRUNC(DBMS_RANDOM.VALUE(1,1000)) FROM du…
SQLの INTERSECT の使い道って何だろう？と。。。 よくあるテーブルの抽出結果を任意のテーブルに insert するのは、 INSERT INTO tableA( column1 ,column2 ) SELECT column1 ,column2 FROM tableB 登録で重複が起きる時、UPSERT文や以下のようなMERGE文、 …
先日、Oracleで１つにカンマ区切り文字列を行変換する例を書いた 文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録１つではなく、複数のカンマ区切り文字列から、TABLE を作成する方法は、以下のように、 TABLE(CAST(MULTISET( 区切…
以下のテーブルがある。 JOBROUTE テーブル id STATION CAR TRAIN WALK 1 新宿 5 12 120 2 渋谷 7 18 130 このCAR, TRAIN, WALK 列をUNPIVOTする。 SELECT id, STATION, M_TYPE, T_MINUTES FROM JOBROUTE UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WAL…
文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録 を書いたので、PostgreSQL で同じことをしてみる。方法は２通り。string_to_array() を使う。 WITH strings AS ( SELECT UNNEST(string_to_array('A,B,C', ',')) AS split_value ) S…
UNPIVOT に近い、わざわざSQLで行うことがナンセンスな気もしたが、 split 対象の文字列を mybatis などから動的パラメータとして指定して結果を他のテーブルと結合して 処理するＳＱＬなど応用範囲は広がると思った。Oracle の場合のサンプル’A,B,C' をカン…
WITH句を使った検索クエリは、 WITH xxxx AS ( ～ ) SELECT * FROM xxxx のように、WITH句の後で、SELECT ～ FROM with句で定義した名称 と書くものだが、 INSERT INTO ～ SELECT で使用する場合、 INSERT INTO の前に書くのか？、後ろに書くのか？どうもデ…
Oracleでは、 SELECT * FROM dual は、列名＝'DUMMY' 値＝'X'の1行を返す。PostgreSQL では、、、 dual は存在しないのでFROM句を省略するのだが、 SELECT * とすると、 SQLエラー [42601]: ERROR: テーブル指定のないSELECT *は無効です になる Oracleと同…
REGEXP_REPLACEで使用できるフラグは、 Oracle とPostgreSQLで以下の差がある。Oracle も PostgreSQL も同じフラグ c 大文字小文字を区別する一致 i 大文字小文字を区別しない一致 Oracle のフラグ n ドット演算子(.)を改行文字と一致させることができる x …
結合しない対象テーブル１個だけなら、GROUP BY 句と COUNT関数を使うが、 JOIN で結合したときの展開されるレコード数を求めるあるいは使用した クエリはどうずれば良いか？ 目的が、 結合元テーブルの任意カラムの数値 ／ 結合したときの展開されるレコー…
先日、PostgreSQL テーブルのコメント一覧を抽出 - Oboe吹きプログラマの黙示録 を書いたので、 今度は、テーブルの列の定義とコメント一覧を抽出 任意のスキーマ、テーブルを指定して次のSQLで求める。スキーマ名＝'public' テーブル名＝'t_items' SELECT a…
設計者がつけるテーブルの論理名もしくはコメントの一覧を抽出する方法CREATE TABLE 文で設計者が命名するテーブル論理名を付けるのは見たことがなく COMMENT ON TABLE 対象テーブル IS 'コメント’ ; でコメントを付与しているであろう。 テーブル名とコメン…
PosetgreSQL 10以降では、serial の代わりに GENERATED AS IDENTITY が使える。 CREATE TABLE user( id BIGINT GENERATED ALWAYS AS IDENTITY, -- 以降省略 -- シーケンス値の開始から省略せずに書くなら id BIGINT GENERATED ALWAYS AS IDENTITY( INCREMENT…
OVER() の PARTITION BY でグルーピングした中で、ORDER BY した先頭を抽出する。DBは、Oracle 、PostgreSQL、MySQL も同じ例）テーブル JOB_RESULTS を JOB_CD 列でグルーピングして POINT列 の ORDER BY DESC の結果、先頭を抽出する。ROW_NUMBER() を OVE…
1~1000 の間の乱数を１００個作る。 対象：Oracle WITH generate(id, rnum) AS ( SELECT 1, TRUNC(dbms_random.value(1,1000 )) FROM dual UNION ALL SELECT id + 1, TRUNC(dbms_random.value(1,1000 )) FROM generate WHERE id + 1 <= 100 ) SELECT rnum FR…
WITH 句を複数記述する場合は、カンマで区切って WITH 宣言を書かずに次のWITH句を 記述する。PostgreSQL の例。１から８までの数値IDのテーブルに、乱数発生のテーブルのIDが奇数で外部結合させる。 WITH RECURSIVE generate1(id) AS ( SELECT 1 UNION ALL …
表題の件、では代わりにストアド FUNCTION でも代替案があるのだろうが、 ストアド FUNCTION 定義の権限がない時、 WITH句で対応する方法がある。数値の例 WITH generate_series(n) AS ( SELECT 1 FROM dual UNION ALL SELECT n + 1 FROM generate_series WH…
実行計画を調査して、ヒント句を付けるなどの調整も検討が必要であるが、 ROW_NUMBER() でページングでをする場合のSQLは、以下のようになる。１ページ目として先頭１行目～１０行目 SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY avrg DESC, point DE…
Oracle は、SQLバインド変数の上限を超えると、 ORA-01745: ホスト/バインド変数名が無効です。 が発生する Oracle のバインド変数の上限は、32768PostgreSQL は、32767MySQL はクエリの上限サイズで制限を見た方が良いみたい show variables like 'max_allo…
以下のようなテーブルがあるとき、 CREATE TABLE worktables ( id uuid DEFAULT gen_random_uuid() NOT NULL, wname varchar(32) NOT NULL, ktype NUMERIC NOT NULL, point NUMERIC DEFAULT 0 NOT NULL, at_create date NOT NULL, CONSTRAINT worktables_pke…
MySQL 、PostgreSQL についての UPSERT 文はこれまでに以下を書いてきたが、 Oracle の UPSERT （MERGE）を書いてなかったので、書き方を書いておくことにした。MySQL で UPSERT する時に考えること - Oboe吹きプログラマの黙示録PostgreSQL のアップサート…
PostgreSQL の場合現在の接続状況を見る。 SELECT * FROM pg_stat_activity 現在接続中のセッションは、 WHERE state = 'active' あるいは、 WHERE xact_start IS NOT NULL で、抽出する。SELECT pg_terminate_backend(PID) で強制的に切断する。 MySQLの場…
PostgreSQL 8.4 以上で使えるようになったウィンドウ関数 テーブルのレコード総数を SELECT句に含めたい場合 COUNT(1) OVER() を指定する。 SELECT *, COUNT(1) OVER() AS total FRON items これは、MySQL でも使える。同様に行番号を SELECT句に付与したい…
MySQL の GROUP_CONCAT あるいは、PostgreSQL の STRING_AGG から、CSV１行を作る。 作ってSELECTした文字列は、先日書いた原始的なメソッド CSV形式の１行分の文字列からList<String>への変換 - Oboe吹きプログラマの黙示録 を使って、List<String> を取得する。任意の列で</string></string>…
collate utf8_unicode_ciutfbm4 のキャラクタコードに対してこの collate を指定するとエラーになる、 Error Code: 1253. COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4' 以下でデーターベースがどうなっているか確認する SHOW VARI…
Java8 以降、java.time.LocalDate と java.time.LocalDateTime の最小値／最大値は、 LocalDate.MIN → -999999999-01-01 LocalDate.MAX → +999999999-12-31 LocalDateTime.MIN → -999999999-01-01T00:00 LocalDateTime.MIN → +999999999-12-31T23:59:59.9999…
PostgreSQL とは違って、ルートを＄文字で指定して、"->" による連結ではなく JSON列名 -> path あるいは、 JSON列名 ->> path で、文字列とての参照になる。 path 部分が、＄から始まってJSONキーを '.' ピリオド区切り文字で指定する。例） jdataという列…
PostgreSQL の JSON_SET／JSONB_SET と同じ要領で使うと失敗する。 JSON_SET(json_doc, path, val[, path, val] ...) path の指定は、JSONルートの $ から、区切り文字 '.' ピリオドで指定する。 PostgreSQL のようにキーが存在しなかったらセットする／しな…
例） 列名 jdata というJSONB列に以下の json データが入っているとする。 { "fr": 11, "key": "E", "pub": "ara", "item": { "name": "orange", "group": { "x01": "AX193", "x02": "AX248", "c923": "A_982" }, "price": 231, "oprion": " " }, "point": 1…
