前日との比較ピクチャ

P277_SQL実践入門_前日の株価と比較して上下を示す
株価を前日と比較して上下同じを示す

考え方はSELECT句にSELECT句を入れて全体の出力に合わせて比較するというC言語でループを考えている人にとっては理解に苦しむSQLクエリである。
ぐるぐる回すループを使わなくてもSELECTでループみたいなことができることを示した例。

基本のテーブル

/*


日付で比較して、
*/
CREATE TABLE Stocks(
brand VARCHAR(8) NOT NULL,
sale_date DATE NOT NULL,
price INT NOT NULL,
--CONSTRAINT pk_Stocks Primary key(sale_date)
)

INSERT INTO Stocks VALUES('A鉄鋼','2008-07-01',1000),
('A鉄鋼','2008-07-04',1200),
('A鉄鋼','2008-08-12',800),
('B商社','2008-06-04',3000),
('B商社','2008-12-31',3000),
('C電気','2008-07-01',9000),
('D産業','2008-06-04',5000),
('D産業','2008-06-05',5000),
('D産業','2008-06-06',4800),
('D産業','2008-12-01',5100)

SELECT brand,sale_date,price,
	CASE SIGN(price - 
		(
		SELECT price
			FROM Stocks S1
			WHERE brand = Stocks.brand
				AND sale_date = 
					(SELECT MAX(sale_date)
						FROM Stocks S2
						WHERE	brand = Stocks.brand
							AND sale_date< Stocks.sale_date
					)
		)
	)
	WHEN -1 THEN '↓'
	WHEN 0 THEN '→'
	WHEN 1 THEN '↑'
	END
FROM Stocks

--drop Table Stocks

日付で比較して、

CREATE TABLE Stocks(

brand VARCHAR(8) NOT NULL,

sale_date DATE NOT NULL,

price INT NOT NULL,

--CONSTRAINT pk_Stocks Primary key(sale_date)

)

INSERT INTO Stocks VALUES('A鉄鋼','2008-07-01',1000),

('A鉄鋼','2008-07-04',1200),

('A鉄鋼','2008-08-12',800),

('B商社','2008-06-04',3000),

('B商社','2008-12-31',3000),

('C電気','2008-07-01',9000),

('D産業','2008-06-04',5000),

('D産業','2008-06-05',5000),

('D産業','2008-06-06',4800),

('D産業','2008-12-01',5100)

SELECT brand,sale_date,price,

CASE SIGN(price -

(

SELECT price

FROM Stocks S1

WHERE brand = Stocks.brand

AND sale_date =

(SELECT MAX(sale_date)

FROM Stocks S2

WHERE brand = Stocks.brand

AND sale_date< Stocks.sale_date

)

WHEN -1 THEN '↓'

WHEN 0 THEN '→'

WHEN 1 THEN '↑'

END

FROM Stocks

--drop Table Stocks

内部結語INNERJOIN

--分解して、同じテーブル同士で比較する
SELECT *
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date< S.sale_date

--分解して、同じテーブル同士で比較する

SELECT *

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date< S.sale_date

MAX

--Sの最大日がでるが1件のみ
SELECT MAX(S.sale_date)
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date< S.sale_date

	--Sの最大日がでるので、会社でまとめると会社分が出力される。ただし、同日はでない
	--同日が存在する場合はS2.sale_date<= S.sale_dateにする

--Sの最大日がでるが1件のみ

SELECT MAX(S.sale_date)

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date< S.sale_date

--Sの最大日がでるので、会社でまとめると会社分が出力される。ただし、同日はでない

--同日が存在する場合はS2.sale_date<= S.sale_dateにする

それぞれ
ただし同日は指定していないので、同日は＜＝としる

SELECT MAX(S.sale_date)
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date < S.sale_date
	Group by S.brand--同日が存在する場合はS2.sale_date <= S.sale_dateにする

SELECT MAX(S.sale_date)

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date < S.sale_date

Group by S.brand--同日が存在する場合はS2.sale_date <= S.sale_dateにする

--S1とS2 でそれぞれ最大日がでる
SELECT MAX(S1.sale_date) ,MAX(S2.sale_date) FROM Stocks as S1
 CROSS JOIN Stocks as S2
WHERE
	S1.brand = S2.brand
	AND S1.sale_date < S2.sale_date--INNER JOINと同じ

--DROP TABLE Stocks

--S1とS2 でそれぞれ最大日がでる

SELECT MAX(S1.sale_date) ,MAX(S2.sale_date) FROM Stocks as S1

CROSS JOIN Stocks as S2

WHERE

S1.brand = S2.brand

AND S1.sale_date < S2.sale_date--INNER JOINと同じ

--DROP TABLE Stocks

--Stocks
--3つのStocksを使っている。
--外側のSTocksと2つ目のStocksで日付を比べて
--外側のStocksと1つ目のStocksで同じ日付で出力する
SELECT brand,sale_date,
	(SELECT S1.sale_date FROM Stocks S1
	WHERE S1.brand = Stocks.brand
		AND 
		sale_date = --Stocks.sale_date
		(SELECT MAX(S2.sale_date) FROM Stocks S2
		WHERE S2.brand = Stocks.brand 
		AND S2.sale_date< Stocks.sale_date
		)
	) as s1_date
FROM Stocks

--Stocks

--3つのStocksを使っている。

--外側のSTocksと2つ目のStocksで日付を比べて

--外側のStocksと1つ目のStocksで同じ日付で出力する

SELECT brand,sale_date,

(SELECT S1.sale_date FROM Stocks S1

WHERE S1.brand = Stocks.brand

AND

sale_date = --Stocks.sale_date

(SELECT MAX(S2.sale_date) FROM Stocks S2

WHERE S2.brand = Stocks.brand

AND S2.sale_date< Stocks.sale_date

)

) as s1_date

FROM Stocks

ピクチャ

ピクチャは海外ドラマ、漫画、英単語語呂合わせ法、アイフォンアプリ等の記事を紹介します

タグ別アーカイブ: 前日との比較

P277_SQL実践入門_前日の株価と比較して上下を示す