カテゴリー別アーカイブ: SQL

[sqlite]日付と時間についての関数

sqliteは以下にある5つの日付と時間を扱う関数がある。

date(timestring, modifier, modifier, …)
time(timestring, modifier, modifier, …)
datetime(timestring, modifier, modifier, …)
julianday(timestring, modifier, modifier, …)
strftime(format, timestring, modifier, modifier, …)

これら全てはゼロ及び修飾文字列で構成されるtime string型を引数としている。strftime関数は第一引数としてtime string型を指定している。
日付と時間の関数はISO-8601に準拠している。date()はYYYY-MM-DD、time()はHH:MM:SS、datetimeはYYYY-MM-DD HH:MM:SSを返す。julianday()はユリウス日を返す。strftime()は第一引数で日付や時刻の形式を受け、標準Cライブラリ及び%fと%Iを加えた変換を行う。

%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

注釈:全ての日付や時刻の関数はstrftime関数によって表現できる。
Function Equivalent strftime()
date(…) strftime(‘%Y-%m-%d’, …)
time(…) strftime(‘%H:%M:%S’, …)
datetime(…) strftime(‘%Y-%m-%d %H:%M:%S’, …)
julianday(…) strftime(‘%J’, …)

時間文字列は以下のどの形式でもよい。
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD
注釈:DDDDDDDDDDはunix時間を表す整数
修飾文字列は以下の種類がある。
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

最初6つの修飾文字列は単に時間や日付を進めたりするためにある。
weekday修飾文字列はNで指定した曜日(日曜日は0、月曜日は1、…)
unixepochは1970年からの秒数である。

【例】
現在の日付
SELECT date(‘now’);

現在の月の最初に日
SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’);

unix時間の1092941466をdatetimeに変換する
SELECT datetime(1092941466, ‘unixepoch’);

現在時刻からunix時間を計算
SELECT strftime(‘%s’,’now’);

US独立記念日から現在までの日数を計算
SELECT julianday(‘now’) – julianday(‘1776-07-04’);

2004年のある瞬間から現在までの秒数を計算
SELECT strftime(‘%s’,’now’) – strftime(‘%s’,’2004-01-01 02:34:56′);

今年10月の最初の火曜日の日付を計算
SELECT date(‘now’,’start of year’,’+9 months’,’weekday 2′);

 

[C#]オラクルとの接続とSQL実行

using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

using (OracleCommand com = new OracleCommand()){
OracleConnection con = new OracleConnection();
con.ConnectionString = "User id=USERID;Password=PASSWORD;Data Source=DBNAME;";

con.Open();
OracleTransaction tra = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
{
com.CommandText = "insert into TEST values(val1,va2)";
com.Connection = con;
com.Transaction = tra;
com.ExecuteNonQuery();
tra.Commit();
}
catch(System.Exception)
{
tra.Rollback();
throw;
}
finally
{
con.Close();
}
}

オラクル 11g新機能(SQL関数)

役に立ったものを紹介する

1.withによる再帰処理(with)
2.列集計追加(rollup)
3.n番目集計(nth_value)



1.再帰を用いたwith
階層問い合わせが容易に行える。使い方によっては最適化問題も解くことができる。
TEST_TABLE

	ID	OYAID
---------- ----------
	 1
	 2	    1
	 3	    1
	 4	    2
	 5	    3
	10
	11	   10
	12	   10

SQL

with rec(ID,oyaID,LV,Path) as
(
	select ID,oyaID,1,to_char(ID) from TEST_TABLE where oyaID is NULL
	union all
	select b.ID,b.oyaID,LV+1,a.Path || '-' || to_char(b.ID)
	from rec a, TEST_TABLE b
	where  a.ID=b.oyaID
)
select * from rec

実行結果

	ID	OYAID	      LV PATH
	 1		       1 1
	10		       1 10
	 2	    1	       2 1-2
	 3	    1	       2 1-3
	11	   10	       2 10-11
	12	   10	       2 10-12
	 4	    2	       3 1-2-4
	 5	    3	       3 1-3-5



2.pivotとrollupを用いた例

SQL

select d,sum(a)/count(a) as a,sum(b)/count(b) as b,sum(c)/count(c) as c
from
(
WITH REC(d,g,val) AS
(
	select 1,1,1 from dual union all
	select 1,1,2 from dual union all
	select 1,2,6 from dual union all
	select 2,2,2 from dual union all
	select 2,3,4 from dual union all
	select 3,3,4 from dual
)
SELECT * FROM REC
pivot (sum(val) for g IN (1 as a,2 as b,3 as c))
)
group by rollup(d)

実行結果

	 D	    A	       B	  C
---------- ---------- ---------- ----------
	 1	    3	       6
	 2		       2	  4
	 3				  4
		    3	       4	  4



3.n番目集計(nth_value)

select d,val
,nth_value(max(val),2)over(partition by d order by val
rows between unbounded preceding and unbounded following) max_2nd
from
(
WITH REC(d,val) AS
(
	select 1,1 from dual union all
	select 1,2 from dual union all
	select 1,6 from dual union all
	select 2,2 from dual union all
	select 2,8 from dual union all
	select 2,4 from dual union all
	select 3,1 from dual union all
	select 3,9 from dual union all
	select 3,4 from dual union all
	select 3,0 from dual union all
	select 4,8 from dual union all
	select 4,9 from dual union all
	select 4,3 from dual
)
SELECT * FROM REC
)
group by d,val
order by d,val

実行結果

	 D	  VAL	 MAX_2ND
---------- ---------- ----------
	 1	    1	       2
	 1	    2	       2
	 1	    6	       2
	 2	    2	       4
	 2	    4	       4
	 2	    8	       4
	 3	    0	       1
	 3	    1	       1
	 3	    4	       1
	 3	    9	       1
	 4	    3	       8
	 4	    8	       8
	 4	    9	       8

参考文献
図でイメージするOracle DatabaseのSQL全集

集合演算子

オラクルのdual表を用いて一時的な表を二つ作成し、集合演算子の確認をする。

SQL

select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual

実行結果

	 2	    3
---------- ----------
	 2	    3
	 3	    4
	 4	    5
	 5	    6

SQL

select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5

以上2つのクエリを用いて、以下にunion, union all, intersect, exceptの実行結果を示す。PostgreSQLではminusについてexceptを用いる。

1.union
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 5	    6

2.union all
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union all
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 2	    3
	 3	    4
	 4	    5
	 5	    6

3.intersect
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
intersect
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 2	    3
	 3	    4
	 4	    5

4.minus
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
minus
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2

オラクル データディクショナリ

オラクルにはテーブルやソースなどの情報をデータディクショナリとして記録してある。

データディクショナリの種類

DBA_*	データベース内全てのオブジェクトに関する情報
ALL_*	自分がアクセスできるオブジェクトに関する情報
USER_*	自分の所有するオブジェクト(自スキーマのオブジェクト)に関する情報
その他	セッションやロール、言語環境に関する情報

主要なデータディクショナリ

DAU	表名	         説明
***	CATALOG	         表・ビュー・索引・シノニムの情報を調べる
***	COL_COMMENTS	 表の列に付けられたコメント情報を調べる(便利!)
**	COL_PRIVS_MADE	 表の列にアクセスできるユーザを調べる
**	COL_PRIVS_RECD	 アクセスできるスキーマの表の列を調べる
***	CONSTRAINTS	 制約の情報を調べる
***	CONS_COLUMNS	 制約が定義されている列の情報を調べる
*	DATA_FILES	 データファイルの情報を調べる
***	ERRORS	         解決されていないエラー情報を調べる
**	EXTENTS	         セグメントに割り当てられたエクステントの情報
**	FREE_SPACE	 表領域内の使用可能エクステント情報
***	INDEXES	         索引の情報
***	IND_COLUMNS	 索引がひも付いている表・列を調べる
***	OBJECTS	         オブジェクトの情報
***	PROCEDURES	 プロシージャに関する情報
*	PROFILES	 プロファイルに関する情報
*	ROLES	         ロールに関する情報
**	ROLE_PRIVS	 ユーザやロールに付与されたロールを調べる
***	SOURCES	         ストアドやファンクションのソースを確認する(便利!)
**	SEGMENTS	 セグメントに割り当てられたエクステントとブロック数を調べる
***	SEQUENCES	 シーケンスに関する情報
***	SYNONYMS	 USER_:プライベートシノニムを確認する
**	SYS_PRIVS	 ユーザとロールに付与されたシステム権限の情報を示す
***	TABLES	         表に関する情報
**	TABLESPACES	 表領域に関する情報
***	TAB_COLUMNS	 表の列に関する情報
***	TAB_COMMENTS	 表に付けられたコメントに関する情報(便利!)
**	TAB_PRIVS_MADE	 スキーマにアクセスできるユーザを調べる
**	TAB_PRIVS_RECD	 アクセスできるスキーマを調べる
*	TEMP_FILES	 一時表領域に属するファイル情報を調べる
***	TRIGGERS	 トリガーに関する情報
**	TS_QUOTAS	 ユーザの表領域に対する領域割り当て制限情報
***	TYPES	         タイプに関する情報
***	USERS	         ユーザに関する情報
***	VIEWS	         ビューに関する情報

例えばパッケージPK_TESTのソースを取得したいときは、
select TEXT from all_sources where NAME=’PK_TEST’

SQL 基本(select以外)

create table TEST_TABLE(val1 number,val2 varchar(10)var3 date)
drop table TEST_TABLE

insert into TEST_TABLE(val1,val2) values(10,’val2′)
insert into TEST_TABLE values(10,’val2′,sysdate)

update TEST_TABLE set val2=’updated’,val3=sysdate where val1=10

delete from TEST_TABLE where val1=10

delte from TEST_TABLE where (val1,val2,val3…)IN
( select val1,val2,val3…
from …
)

alter table TEST_TABLE add(val4 number)
alter table TEST_TABLE modify(val4 date)
alter table TEST_TABLE drop(val4)
alter table TEST_TABLE RENAME COLUMN val3 to day

alter table TEST_TABLE add primary key (
val1,
val2,
val3,

)