SQL

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

SQL

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

【Hive】【SQL】ROW_NUMBER + PARTITION BY

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

【SQL】EXPLAINステートメント

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

【SQL】相関サブクエリ / 自己相関サブクエリ のサンプル

SQL

■ はじめに https://blogs.yahoo.co.jp/dk521123/35761987.html の続き。 ■ サンプル1:変化(増加/変化なし/減少) 使用テーブル / データ * Oracle 11g テーブル -- 年間売上 CREATE TABLE "SALES" ( "YEAR" INT, "SALE" INT, PRIMARY KEY ("YEAR") ); # …

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

SQL

■ はじめに * 例えば、全体数とその母数からさらに条件を付けてカウントしたい場合に それをサブクエリを使わずに、一回で行う方法を考える 解決案 COUNT (条件 OR null) でカウント可能。 ■ 実行環境 * DB : MySQL5.7 ■ サンプルデータ テーブル -- 学生 CR…

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

SQL

■ はじめに 初めの数文字が一致してたら同一データと見なし、重複を排除するSQL文を考える 解決案 以下を使えば可能。 * サブクエリ * LEFT() * GROUP BY サブクエリ https://blogs.yahoo.co.jp/dk521123/35657803.html GROUP BY https://blogs.yahoo.co.jp/…

【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でループ→ 相関サブクエリ」とあるので、 相関サブクエリについて、学ぶ。 ■ 相関サブクエリ / 自己相関サブクエリ について https://blogs.yahoo.co.jp/dk521123/35657803.ht…

【SQL】SQLを書くコツ

SQL

はじめに * 書籍やサイトなどで、気になった事をメモる。 【1】SQL文を考える順番:FROM句から書く 以下の順番で考える。 1) FROM 2) WHERE 3) GROUP BY 4) HAVING 5) SELECT 6) ORDER BY 【2】SQLの基本原理:SQLを集合指向と捉える * C言語/Javaなどのプ…

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

SQL

■方法1 * ただし、この方法には欠点がある SELECT * FROM テーブル名 WHERE カラム名 = (SELECT MAX(カラム名) FROM テーブル名 WHERE カラム名 <= 対象日付) 欠点 * メインのSQLに他に条件を加えると「(SELECT MAX(カラム名)・・・」の方にも 同条件を追加…

【SQL】 サブクエリ / 副問合せ ~SELECT文中のSELECT文~

SQL

■ 副問合せ(サブクエリ)とは? * SELECT文を入れ子にして記述する構文 ■ 使いどころ * 抽出した結果をさらに、絞り込みたい際に使用 使用例 例1: 「最新日付データ」の取得 http://blogs.yahoo.co.jp/dk521123/34098423.html 例2: 「過去直近データ」の取得…

【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

■方法 SELECT * FROM テーブル名 AS A1 INNER JOIN (SELECT 主キー, MAX(カラム名) AS latest FROM テーブル名 GROUP BY 主キー)) AS A2 ON A1.主キー = A2.主キー AND A1.カラム名 = A2.latest ■サンプル * PostegreSQLの場合 サンプルデータ -- テーブル定…

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

SQL

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

【SQL】SQLで階層構造データを扱う

SQL

http://www.geocities.jp/mickindex/database/db_tree_ns.html がよく分かる 参考文献 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

文字列の取り出し * LEFT関数、RIGHT関数、SUBSTRING関数 サンプル SELECT LEFT('東京都世田谷区',3) /* → 東京都 (左から3文字)*/ SELECT RIGHT('東京都世田谷区',4) /* → 世田谷区 (右から4文字)*/ SELECT SUBSTRING('今日は金曜日', 4, 2) /* → 金曜 (4文…

【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:正規表現を利用する * 正規表現の[Xx]を利用する サンプル SELECT * FROM TestTable WHERE Name LIKE '%…

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

SQL

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

【SQL】ページング ~ ROW_NUMBER ~

SQL

■ ページング 実務的 * ORDER BY句を使用するので、パフォーマンスが心配 * しかし、データ量が多い時は、I/O的にも有効なので、システムを考慮して適用した方がいいかも。 技術的 * Sql Serverには、MySQLやPostgresql にある LIMIT() がない * Sql Server …

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

SQL

【0】 使用しているテーブル (SQL Server) -- Orders CREATE TABLE [Orders]( [orderID] [bigint] NOT NULL, [customerID] [varchar](15) NOT NULL, [orderDateTime] [datetime] NOT NULL, [Remark] [nvarchar](100) NULL, CONSTRAINT [PK_Orders] PRIMARY …

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

SQL

WITH 句 * 共通テーブル式 (CTE, Common Table Expression) と呼ばれる一時的な名前付き結果セットを指定できる。 * WITH句を使って、クエリに名前をつける http://msdn.microsoft.com/ja-jp/library/ms175972(v=sql.90).aspx 使用上の注意 * SQL Server 200…

【SQL】【ストアド】繰り返し処理 ~ WHILE文 / BREAK文 / CONTINUE文 ~

SQL

■ WHILE文 サンプル DECLARE @counter int; SET @counter = 0; WHILE (@counter < 100) BEGIN /* 繰り返したい処理 */ SET @counter = @counter + 1; END ■ BREAK文 サンプル DECLARE @counter int; SET @counter = 0; WHILE (@counter < 100) BEGIN /* 繰り…

【SQL】テーブルの結合 ~ 外部結合・LEFT/RIGHT/FULL JOIN ~

SQL

■ はじめに https://dk521123.hatenablog.com/entry/2010/02/05/131206 の続き。 今回は、外部結合について、扱う。 目次 【0】外部結合 の種類 【1】左外部結合(LEFT OUTER JOIN) 【2】右外部結合(RIGHT OUTER JOIN) 【3】完全外部結合(FULL OUTER…

【SQL】SQL文が実行されるまでの工程

■ はじめに * DBパフォーマンスを行うためには、 まず、どのような工程でSQL文が実行されるかを知ることが重要となってくる 目次 【1】SQL文が実行されるまでの工程 1)SQL文の解析処理(PARSE) 2)SQL文の書き換え 3)実行プランの作成 【3】クエリ・…