SQL

【Hive】【SQL】全く関係ないテーブルを結合することを考える

■ はじめに Hive と Redshift で カウントなどの集計情報するのに 全く関係ないテーブルを結合したので、 そのことについて、メモっておく。 (もっといい方法があれば、随時、更新していく) 目次 【1】結合方法 方法1:JOINを使った結合 【2】サンプル …

【SQL】Window関数 / 分析関数 ~ LAG / LEAD ~

SQL

■ はじめに SQL の Window関数 (分析関数) について扱う。 PostgeSQLだけでなく、Redshiftでも使える。 今回は、その中で、 LAG / LEAD に絞る。 なお、ROW_NUMBER / RANK については、 以前やった以下の関連記事を参照のこと。 ROW_NUMBER / RANK + PARTITI…

【SQL】SQLで変化(増加/変化なし/減少)について考える

SQL

■ はじめに SQLで変化(増加/変化なし/減少)について考える 目次 【1】ポイント 1)過去データと比較するために自己結合する 2)データ仕様上、歯抜けがあるかどうかを確認 【2】Oracle 11g 例1:年間売上の変化(増加/変化なし/減少) 例2:年間売上…

【Hive】【SQL】差集合 ~ EXCEPT / MINUS etc ~

■ はじめに https://dk521123.hatenablog.com/entry/2021/02/11/233633 で記載した警告文 ~~~~~~~ unix_timestamp(void) is deprecated. Use current_timestamp instead. ~~~~~~~ に対応し、クエリを修正したのだが、 修正前と修正後の確認で、SQL文「MINUS…

【Hive】【SQL】ROW_NUMBER / RANK + PARTITION BY

■ はじめに Hive でデータが積み重なるようなテーブル (例えば、ユーザ情報で入会・退会するようなデータ)に対して ユーザの最新/過去直近情報を取得するのに、 ROW_NUMBER + PARTITION BY を使ったので、メモする。 ついでに、RANK + PARTITION BY も載せ…

【SQL】EXPLAINステートメント

■ はじめに https://dk521123.hatenablog.com/entry/2020/02/22/002139 https://dk521123.hatenablog.com/entry/2020/08/31/171754 で、Redshiftの勉強した際に、パフォーマンスの分析する際に EXPLAINステートメントがでてきた。 以前、やったはずだが、す…

【SQL】グループ内のMAXデータを取得する

SQL

■ はじめに 性別・部署別の最高齢を取得するSQLを考える 相関サブクエリ / 自己相関サブクエリを使う。 相関サブクエリ / 自己相関サブクエリについては、 以下の関連記事を参照のこと。 https://dk521123.hatenablog.com/entry/2016/01/23/230608 目次 【1…

【SQL】複数の集計を1回のSQL文で行う方法を考える

SQL

■ はじめに 例えば... 全体数とその母数からさらに条件を付けてカウントしたい場合に それをサブクエリを使わずに、一回で行う方法を考える 目次 【1】解決案 【2】サンプル 【1】解決案 COUNT (条件 OR null) でカウント可能。 【2】サンプル * 全体数…

【SQL】初めの数文字が一致してたら同一データと見なすSQLを考える

SQL

■ はじめに 初めの数文字が一致してたら同一データと見なし、 重複を排除するSQL文を考える 目次 【1】解決案 【2】サンプル 【1】解決案 以下を使えば可能。 * サブクエリ * LEFT() * GROUP BY 【2】サンプル * DB : MySQL5.7 テーブル CREATE TABLE `m…

【SQL】ALTER TABLE ~ テーブル制約変更 編 ~

SQL

■ はじめに https://dk521123.hatenablog.com/entry/2016/06/16/220624 の続き。 今回は、テーブル制約変更を扱う 目次 【1】PRIMARY KEY制約 【2】UNIQUE制約 【3】Not null制約 【4】制約の削除 * 各制約(= CONSTRAINT)については、以下の関連記事を…

【SQL】ALTER TABLE ~ テーブル変更 編 ~

SQL

■ はじめに https://dk521123.hatenablog.com/entry/2010/07/09/201233 の続き。 今回は、テーブル変更するための「ALTER TABLE」を扱う 目次 【1】表名を変更する 【2】表の項目追加 【3】表の項目削除 【4】表の項目のデータ型 / 文字列長の変更 【5…

【SQL】相関サブクエリ / 自己相関サブクエリ

SQL

■ はじめに https://gihyo.jp/dev/serial/01/sql_academy2/000901 などで、 「SQLでループ→ 相関サブクエリ」とあるので、 相関サブクエリについて、学ぶ。 目次 【1】相関サブクエリ / 自己相関サブクエリ 【2】相関サブクエリの捉え方 【3】使用上の注…

【SQL】SQLを書くコツ

SQL

■ はじめに * 書籍やサイトなどで、気になった事をメモる。 目次 【1】FROM から書く 【2】SQLを集合指向と捉える 【3】ループは GROUP BY句と相関サブクエリで置き換える 【1】FROM から書く SQL文を考える順番は、以下の順番で考える。 SQL文を考える…

【SQL】過去直近データを取得するには

SQL

■ はじめに SQLで、過去直近データを取りたい時って多々あるのでメモ。 目次 【1】方法1 1)構文 2)欠点 【2】方法2 1)構文 2)サンプル 【3】方法3:ROW_NUMBER + PARTITION BY 【1】方法1 * ただし、この方法には欠点がある 1)構文 SELECT…

【SQL】 サブクエリ / 副問合せ

SQL

■ はじめに SQL の サブクエリ について、メモ。 目次 【1】副問合せ(サブクエリ)とは? 【2】使いどころ 【3】使用例 例1: 「最新日付データ」の取得 【1】副問合せ(サブクエリ)とは? * SELECT文を入れ子にして記述する構文 => SELECT文中のSELE…

【Ant】 Ant で、SQL を実行する

sqlタスク 仕様 http://www.jajakarta.org/ant/ant-1.6.1/docs/ja/manual/CoreTasks/sql.html サンプル : PostgreSQL build.xml * SQL文 <project basedir="." default="sql.sample" name="SampleJavaCodes"> <target name="sql.sample"> </target></project>

【SQL】レコードが存在していなかったら、INSERTするには

SQL

レコードが存在していなかったら、INSERTするには * 条件に「NOT EXISTS」を利用する => WHERE NOT EXISTS (SELECT * FROM 【テーブル名】 WHERE 【主キー名】='【主キー値】'); サンプル 例1:PostgreSQL 使用するテーブル CREATE TABLE person ( id charac…

【SQL】最新日付データを取得するには

SQL

■ はじめに SQLで、最新日付データを取りたい時って多々あるのでメモ。 目次 【1】方法 【2】サンプル 【3】補足:過去直近データを取得する場合 【1】方法 SELECT * FROM テーブル名 AS A1 INNER JOIN (SELECT 主キー, MAX(カラム名) AS latest FROM テ…

【SQL】アクセス制限 ~GRANT文 / REVOKE文~

SQL

アクセス制限 http://www.techscore.com/tech/sql/10_01.html/ が、図もあって、一番わかりやすかった。 GRANT文 * 権限を付与する => 表の利用者に対し,表への問合せ,更新,追加,削除などの操作を許可する場合に使用 構文 GRANT 【権限名】 ON 【テーブル名…

【SQL】SQLで階層構造データを扱うことを考える

SQL

■ はじめに SQLで階層構造データを扱うことを考える 参考文献 http://gihyo.jp/dev/serial/01/sql_academy2/000501

【SQL】【ストアド】変数の扱い

SQL

■ 定義 DECLARE文と変数名の前には「@」をつける サンプル DECLARE @i INT ■ 設定 SET文をつける サンプル SET @i = 0 ■ 戻り値を受け取る 構文 EXECUTE @【戻り値の変数名】 = 【ストアド名】 (【引数1】, 【引数2】 ...); サンプル DECLARE @returnValue i…

【SQL】【ストアド】文字列の扱い

SQL

■ はじめに SQLでの文字列の扱いについてメモ。 目次 【1】文字列の取り出し 【2】文字列の長さ 【1】文字列の取り出し 以下の関数が使える 1)LEFT関数 2)RIGHT関数 3)SUBSTRING関数 サンプル /* → 東京都 (左から3文字)*/ SELECT LEFT('東京都世田…

【SQL】データあればUPDATE、なければINSERT ~ MERGE文 (マージ文) ~

SQL

MERGE文 * SQL標準(SQL:2003規格) * UPDATE と INSERT を組み合わせた造語として、UPSERT文ともいう * Merge = 「結合する」「吸収する」 対応DB * SQL Server2008以降 * Oracle 9i以降 など多数 ※ 文法が異なるので注意 SQL Server 構文 MERGE INTO 主表 US…

【SQL】データあればUPDATE、なければINSERT ~ @@ROWCOUNT ~

SQL

SQL Server2005以前 / Oracle 9iより前 * UPDATEしてみて、実行件数が0件なら、INSERTする SQL Server * @@ROWCOUNT を利用する サンプル DECLARE @name nvarchar(50) SET @name = 'Tom' DECLARE @address nvarchar(50) SET @address = '1-23 London' DECLAR…

【SQL】整合性制約 ~ Integrity Constraints ~

SQL

■ はじめに https://dk521123.hatenablog.com/entry/2016/07/08/234918 で扱ったSQLの制約(Constraints)について、学びなおす 補足:Constraint の英語的意味 (名) 制約、強制 【動】Constrain = 抑制する、抑止(よくし)する [語源] con(完全に)+strain(…

【SQL】促音・拗音を区別せず検索する

SQL

■ はじめに 促音・拗音、つまり小さい文字 「ぁ」「ぃ」「ぅ」「ぇ」「ぉ」「っ」「ゃ」「ゅ」「ょ」 を大きい文字と区別せずに、検索する方法を考える 目次 【1】案1:正規表現を利用する 【2】案2:検索用の項目を追加 【1】案1:正規表現を利用する * …

【SQL】バルク・インサート ~Bulk Insert~

SQL

Bulk Insert * 一括でテーブルに挿入できる * しかも早い!!! 使用上の注意 * 指定できるフィールド数は 1024 個まで http://msdn.microsoft.com/ja-jp/library/ms188365.aspx * 取込元のファイルは、DBエンジンが動いてるマシンから参照出来ないとファイ…

【SQL】ページング ~ ROW_NUMBER ~

SQL

■ はじめに SQL文で、ページングを考える 目次 【1】ページング 1)実務的視点 2)技術的視点 【2】ROW_NUMBER 1)構文 2)補足:ROW_NUMBER + PARTITION BY 【3】サンプル 補足:使用しているテーブルについて 【1】ページング 1)実務的視点 * OR…

【SQL】順位付け・ランキング ~ RANK / DENSE_RANK ~

SQL

■ はじめに SQL で、順位付け・ランキングをする際に使用する RANK / DENSE_RANK について、学ぶ。 目次 【1】RANK / DENSE_RANK 1)RANK 2)DENSE_RANK 【2】RANK + PARTITION BY 【3】サンプル - SQL Server 使用しているテーブル 例1:顧客ごとの売…

【SQL】 WITH句 ~ 共通テーブル式 ~

SQL

■ はじめに WITH についてメモ。 目次 【1】WITH 句 【2】使用可能なDB 【3】使用上の注意 【4】構文 【5】サンプル 例1:親子関係に使用 例2:最新データによる年間売上の増減 【1】WITH 句 * 共通テーブル式 (CTE, Common Table Expression) と呼…