GET_AGE

CREATE OR REPLACE FUNCTION GET_AGE
    (
    IN_BIRTHDAY         IN DATE                  -- 生年月日
  , IN_DESIGNATED_DATE  IN DATE DEFAULT SYSDATE  -- 指定日
    )
    RETURN NUMBER -- 年齢(年のみ)
--
-- 生年月日から本日までの満年齢の取得。
--
IS
BEGIN
    RETURN(TRUNC(MONTHS_BETWEEN(IN_DESIGNATED_DATE, IN_BIRTHDAY) / 12)) ;
END GET_AGE ;
/
SHOW ERROR FUNCTION GET_AGE
--  PUBLIC SYNONYM に
-- ↓実行権をPUBLICにし、インスタンスの全スキーマから実行可能に
-- https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_9014.htm#i2155015  SQL language reference
GRANT EXECUTE ON GET_AGE TO PUBLIC ;
--
-- ↓PUBLIC SYNONYM に
-- http://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_7001.htm#CJAJCDDF  SQL language reference
CREATE OR REPLACE PUBLIC SYNONYM GET_AGE FOR GET_AGE ;

-- 使用例
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/03/01', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;

GET_AGE

CREATE OR REPLACE FUNCTION GET_AGE
    (
    IN_BIRTHDAY         IN DATE                  -- 生年月日
  , IN_DESIGNATED_DATE  IN DATE DEFAULT SYSDATE  -- 指定日
    )
    RETURN NUMBER -- 年齢(年のみ)
--
-- 生年月日から本日までの満年齢の取得。
--
IS
BEGIN
    RETURN(TRUNC(MONTHS_BETWEEN(IN_DESIGNATED_DATE, IN_BIRTHDAY) / 12)) ;
END GET_AGE ;
/
SHOW ERROR FUNCTION GET_AGE
--  PUBLIC SYNONYM に
-- ↓実行権をPUBLICにし、インスタンスの全スキーマから実行可能に
-- https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_9014.htm#i2155015  SQL language reference
GRANT EXECUTE ON GET_AGE TO PUBLIC ;
--
-- ↓PUBLIC SYNONYM に
-- http://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_7001.htm#CJAJCDDF  SQL language reference
CREATE OR REPLACE PUBLIC SYNONYM GET_AGE FOR GET_AGE ;

-- 使用例
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/03/01', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;

お金がない。給料日前の社会人の共通の悩みですね。
今月もBigQueryで実行しすぎて、こんなにも出費がかさんでいる、、給料が入るまで白湯しか飲めない。。。今回はそんなそこのあなたにお送りします。

概要

BigQueryで、日付単位でテーブルを分ける機能、分割テーブル（パーティション）の読み込み方法をまとめてました。

参考ページ

https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja&_ga=2.63498345.-710407308.1550214548&_gac=1.246598192.1550709266.EAIaIQobChMIxqaXjMrL4AIVlaqWCh2M1gzIEAAYASAAEgJXCfD_BwE

使用テーブル: shopping

日時が記載されている適当なテーブルを作成しました。

user_id	last_date	item
A	2019/1/29	ちくわぶ
A	2019/12/30	白菜
B	2019/10/31	みかん
B	2019/6/29	りんご
C	2016/1/30	メロン
C	2019/12/30	みかん
B	2019/10/31	りんご
C	2019/6/29	ちくわぶ
A	2019/1/30	メロン
B	2019/12/30	ねぎ
B	2019/10/31	みかん
C	2019/6/30	りんご

読み込み手順１:自動認識でファイルを読み込ませる

BigQueryの画面から「テーブルを作成」を選択（以下画像）

ポップアップした画面で、今回はローカル保存しているshopping.csvを「ファイル名を選択」で指定します。
任意の「テーブル名」をつけ、最後にスキーマの「自動検出　スキーマと入力パラメータ」の欄にチェックします。今回はパーティションとクラスタの設定は「パーティショニングなし」を選びます。ここで「取り込み時間により分割」を選ぶと、実行を行った日付で一つパーティショニングが行われます。last_dateに関係なく、本日の日付で一つの分割テーブルを作るとき使います

通常通りshoppingのテーブルが取り込まれました。日付の入ったlast_dateのカラムはDATE型として認識されています。

読み込み手順2:テーブルを分割（パーテーショニング）する

BigQuery

CREATE TABLE
  test1.p_shopping
PARTITION BY
  last_date AS
SELECT
  *
FROM
  test1.shopping;

上記のコードで、
CREATE TABLE 'データセット名'.'テーブル名'　PARTITION BY '日付型のカラム' AS .....と書き出すことで、作成されるファイルが分割されます。ちなみにこの実行ではテーブル全体が指定されるため、処理量は節約されません涙

こうしてできた分割テーブルは、BigQuery上のアイコンではおしゃれに表示されます。

p_shoppingのテーブル詳細を見てみると、表情報の欄にしっかり、フィールドで分割　'last_date'と記載されてます。
今後このカラムで日付指定すると、通常より少ない処理容量となります。

実践：分割テーブルを使ってみる。

先ほど作成した分割テーブルを使い、last_dateが”2019-01-30”の行を抜き出してみます。

BigQuery

SELECT
  *
FROM
  test1.p_shopping
WHERE
  last_date ='2019-01-30';

ありがたいことに、元々の264Bのテーブルがこんなにも節約されました涙

実験:分割テーブルできるカラムと、できないカラム

どうせなら日付以外でも分割テーブルが作れたらいいなぁ、と思い試してみました。

DATE型を二つ指定しても分割できるのか　　→　無理

試しにDATE型のカラムが二つあるテーブルを分割テーブルにできるか、確認してみました。
利用したのは以下のテーブルです。

user_id	last_date	p_date	item
1	2016-01-30	2017-12-30	メロン
2	2017-12-30	2017-12-31	白菜
3	2016-10-31	2017-08-31	りんご
4	2017-06-30	2018-01-31	りんご

PARTITION BY にカラム二つを指定し、実行してみます、が．．．

PARTITION BYは一つしか使えません、二つあるよ！とエラーが出ました。

TIMESTAMP型でも日付で分割してくれるのではないか　　→　無理

shoppingのテーブルのlast_dateをTIMESTAMP型へ変更して、PARTITION BYを使い分割テーブル化を試みましたが、、、

BigQuery

CREATE TABLE
  test1.shopping_1 AS
SELECT
  user_id,
  TIMESTAMP(last_date) AS last_date,
  item
FROM
  test1.shopping

分割テーブルは現在、DATE型のみ対応しています。。。

まとめ

分割テーブル（パーテーション）を使うと、利用するテーブルの容量が限定されるため、課金対象となるテーブルの作成容量の節約になります。
しかし、現状分割対象は日付のみで、利用者によってはあまり使い勝手がよくない機能なのかもしれません。

20190227現在なので、更新すると、DATE型以外も使えるm(_ _ )m

BIツールとかでプレースホルダに入力された日付から

• 当日
• 月初
• 月末
• 四半期の始まりの日
• 四半期の終わりの日

をいっぺんにだすクエリかいた。
これをwithなんかに書いとくとその後のクエリが楽になる
（いっぺんに全部出すなっていう話ではある）
いちおうpostgress用

    SELECT
    '{{ date }}'::date date,
    DATE_TRUNC('month', '{{ date }}'::date)::date month_start,
    (DATE_TRUNC('month', '{{ date }}'::date + interval '1 month') - 1)::date month_end,
    CASE EXTRACT(QUARTER FROM date '{{ date }}'::date)
        WHEN 1 THEN DATE_TRUNC('year', '{{ date }}'::date)::date -- 1/1
        WHEN 2 THEN DATE_TRUNC('year', '{{ date }}'::date)::date + interval '3month' -- 4/1
        WHEN 3 THEN DATE_TRUNC('year', '{{ date }}'::date)::date + interval '6month' -- 7/1
        ELSE  DATE_TRUNC('year', '{{ date }}'::date)::date + interval '9month' -- 10/1
    END quarter_start,
        CASE EXTRACT(QUARTER FROM date '{{ date }}'::date)
        WHEN 1 THEN DATE_TRUNC('year', '{{ date }}'::date)::date + interval '3month' - interval '1 minute' -- 3/31
        WHEN 2 THEN DATE_TRUNC('year', '{{ date }}'::date)::date + interval '6month' - interval '1 minute'  -- 6/30
        WHEN 3 THEN DATE_TRUNC('year', '{{ date }}'::date)::date + interval '9month'  - interval '1 minute' -- 9/30
        ELSE  DATE_TRUNC('year', '{{ date }}'::date)::date + interval '12month' - interval '1 minute' -- 12/31
    END quarter_end

なんか他にいいやり方ない？

　　本記事と誰でもすぐわかるデータベース基礎の基礎~　は【学習計画】十週間で知識ゼロからのデータ分析入門の第三週の学習内容です。今後この学習計画について更新していきます。

　　誰でもすぐわかるデータベース基礎の基礎~では、データベースのコンセプトとRDBMSのMySQLのインストール方法について説明しましたが、今日はデータベースの操作とSQLについて説明します。

SQL（Structured Query Language）はRDMBSにおいて、データベースの定義や操作を行うためのデータベース言語です。
（出典: フリー百科事典『ウィキペディア（Wikipedia）』）

　ウィキペディアの定義通り、リレーショナルデータベース、とその中のテーブル、データの参照、変更、削除、計算などを行うために、SQLが必要です。

　Oracle、DB2、Sybase、SQLServer、MySQL、MS Accessなどのデータベースは、SQLの構文が違いますが、基本的には同じです。市場に多くのセルフサービスのBIツールがあって、ドラッグアンドドロップだけでデータ分析ができるにもかかわらず、データエンジニアまたはデータアナリストとして、少しでもSQLを理解しなければいけません。

　市場にSQLに関する書籍やオンラインチュートリアルが増えているので、ここは、ただその学習の要点をまとめて共有したいです。
　この記事は主に以下の方面から説明されています、ご参考までにどうぞ～

1.SQLクイック入門

2.データベースの基本操作

3.テーブルの基本操作

4.データ検索クエリ

---

1.SQLクイック入門

　知識ゼロからSQLを独学する際、よく2つの問題があります。

問題①：

　SQL SeverかMySQLを学ぶ前にデータベースのソフトをインストールする必要があります。初心者にとって、データベースソフトのインストールは時間がかかります。最初仕事のトレーニングを受けた時、2日間SQLをマスターすることが要求されました。結局、SQL Severのインストールにほぼ1日かかってしまいました。前の記事でそれほど複雑ではないMySQLのインストールを紹介しました。

問題②:

　最初SQL構文をたくさん暗記して、でも実際に使う時何もできません。私からいうと、練習しながら覚えることは一番効率的な方法です。 Excelの関数と同じように、その意味と使い方を大体理解した上で、実際使う時にW3cschools（オンライン学習サイト）で調べれば、だんだん身に着けるようになります。

SQL学習

方法①:

MySQLをダウンロードしてインストールしてからビルトインのデータベースを利用して、W3cschoolsに従って練習します。（英語が苦手な方は言語で日本語を選択してください。）

方法②：

無料のオンラインコースを勉強します。二つのサイトをお勧めします。

SQL Tutorial

　このサイトは昔は英語と中国語のバージョンしかありませんが、今一部の内容に日本語のバージョンも利用できます。練習問題と段階的なクイズが用意されており、SQL文を入力してその実行結果を見ることができます。また、覚えない時、Referenceで調べられます。

Progate
　SQL Tutorialと同じく、SQL文を入力してその実行結果を見ることができる一方、解説はとても詳しくわかりやすくて、練習問題を解く際、入力欄や実行結果と一緒に見本や解説も見ることができます。

2.データベースの基本操作

①既存のデータベースを表示する

SHOW DATABASES;

②データベースを新規作成

CREATE DATABASE <database_name>;

③データベースを選択する

USE <database_name>;

④.sqlファイルからSQL文を参照する

SOURCE <.sql file path>;

⑤データベースを削除する

DROP DATABASE <database_name>;

3.テーブル

①既存のデータベース内のテーブルを表示する

SHOW TABLES;

②テーブルを新規作成する

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

各列のデータタイプ（後述）を指定する必要があります。

• INTEGER 整数
• NUMERIC 任意の精度を持つ数字
• CHAR 固定長の文字列
• VARCHAR 可変長の文字列
• DATE 日付

③テーブル内の列の情報を表示する

DESCRIBE table_name;

④既存のテーブルにレコードを追加する

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

カラム名を省略する場合もあります。それで、値が順番にすべてのカラムに追加されます。

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

⑤既存のテーブル内のレコードを変更する

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

⑥テーブル内のレコードを削除する

DELETE FROM table_name
WHERE condition; 

⑦テーブルを削除する

DROP TABLE table_name;

⑧既存のテーブルにカラムを追加、削除、または変更する

• カラムを追加する

ALTER TABLE table_name
ADD column_name datatype;

• カラムを削除する

ALTER TABLE table_name
DROP COLUMN column_name;

• カラムを変更する

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

⑨テーブル名を変更する

RENAME TABLE table_name1 to table_name2;

4.データ検索クエリ

①SELECT

テーブルから指定するカラムのデータを選択する

SELECT column1, column2, ...
FROM table_name;

テーブルのすべてのデータを選択する

SELECT * FROM table_name;

②SELECT DISTINCT

重複値を含めないデータを選択する

SELECT DISTINCT column1, column2, ...
FROM table_name;

③WHERE
先、既存のテーブル内のレコードを変更する時、条件を特定するためのwhereを使いました。

where条件は通常以下のようになります。

• 文字列での比較（text）
• 数値での比較（numbers）
• AND、OR、NOTなどの論理演算

例：

SELECT product_name, product_type
FROM Product;
WHERE product_type = 'clothes';

④GROUP BY
GROUP BYを使うと特定のカラムをキーにした合計値や平均値などが表示される結果となります。一般的に集計関数（COUNT、MAX、MIN、SUM、AVG）と一緒に使用されます。

 SELECT column_name(s)
 FROM table_name
 WHERE condition
 GROUP BY column_name(s);

• COUNT：テーブル内のレコード数（行数）を計算する
• SUM：テーブルの数値列のデータの合計値を計算する
• AVG：テーブルの数値列のデータの平均値を計算する
• MAX：テーブル内の任意のカラムのデータの最大値を求める
• MIN：テーブル内の任意のカラムのデータの最小値を求める

GROUP BYとWHEREを一緒に使用した場合のSELECT文の実行順序：
FROM → WHERE → GROUP BY → SELECT

例：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

⑤Having
Havingは集計関数と一緒に使って、カラムを集計した結果を特定する条件です。HavingとWHEREを区別しなければいけません。

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

例：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

⑥ORDER BY
ORDER BYは取り出した結果をソートします。ASC（昇順）またはDESC（降順）を指定しないと、既定は昇順になります。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

⑦BETWEEN
BETWEENはWHEREと一緒に、指定した範囲のレコードを検索するために使用されます。

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

⑧LIKE
LIKEで特定の文字列の検索を行うことができます。

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

LIKEと組み合わせで使用される2つのワイルドカードがあります。

• ％ —— 0文字以上の任意の文字列
• _ —— 任意の1文字

　具体的な使い方は以下の通り：

⑨IN

WHEREと一緒に使って、複数の値を指定することができます。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

⑩JOIN

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

JOINは、二つ以上のテーブルの値を組み合わせるために使用されます（テーブル間に関連がある場合）。 下図は、4種類のJOINを示しています。

⑪ビュー
ビュー（View）は仮想的なテーブルのことです。 通常のSQLテーブルと同じように、レコードとカラムを含めています。 ビューは常にデータベース内の最新のデータを表示します。

• ビューを作成する

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• ビューを削除する

DROP VIEW <view_name>;

⑫サブクエリ
既存のテーブルを加工した物からデータを取り出したい場合、サブクエリは役に立ちます。
例えば、2018秋と2019春に開かれるコースを検索する場合：

SELECT DISTINCT course_id FROM section
WHERE semester = 'Fall' AND year= 2018
AND course_id IN(
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2019
);

論理演算子による条件の指定

演算子の優先順位

論理演算子の優先順位は,NOT>AND>OR

()を使うと優先順位を変更できる

ORDER BY

1.ORDER BY句では列別名を指定できる
2.ORDER BY句では列の位置を指定することができる。

SELECT name, salary, email
FROM employees
ORDER BY 3 <- email列でソートされる。

問い合わせで取得する行の制限

1.BETWEEN A AND B

注意点　範囲は境界値を含む

2.ESCAPEオプション

いつ使うの？

LIKE演算子で文字列で「％」,「」を取り出したい時
（LIKE演算子では「％」「」をワイルドカードとして扱うためESCAPEオプションを設定する必要がある。）

文法

WHERE 列名 LIKE '文字パターン' ESCAPE 'エスケープ文字'

解説

エスケープ文字には任意の１バイトの文字（¥,$,#,aなど）を指定可能。
エスケープ文字の１つ後の文字を文字列の一部としてみなす。

実例

WHERE name LIKE '100¥%¥_%' ESCAPE '¥';

「100%_」で始まるデータが取り出される。

代用引用符(q)演算子

いつ使うの？

文字列リテラルの一部に「’」を使用したい場合。
（文字列リテラルは「'hogehoge'」といった風にシングルクォーテーションで挟む必要がある。）

文法

q'引用符デミリタ ... 引用符デミリタ'

解説

引用符デミリタには任意のシングルバイト文字やダブルバイト文字、[],{},(),<>の各組み合わせを指定できる。

実例

select name || q'['s salary is]' || salary

's salaryが文字列として出力される。