C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

SQLServer の OVER の設定方法

[トピック内 2 記事 (1 - 2 表示)]  << 0 >>

■95004 / inTopicNo.1)  SQLServer の OVER の設定方法
  
□投稿者/ ファイター (5回)-(2020/06/11(Thu) 08:54:14)

分類:[.NET 全般] 

2020/06/11(Thu) 08:55:08 編集(投稿者)
OVER の使い方を教えてください。

DECLARE @TBL TABLE (
  CDNO INT NOT NULL,
  TMDT DATETIME NOT NULL,
  STC INT NOT NULL
);

WITH
DAT	(CDNO, TMDT, STC)
	AS (
	SELECT	1, '13:47:35.257', 1
	UNION SELECT	1, '14:09:27.983', 4
	UNION SELECT	1, '14:11:40.020', 6
	UNION SELECT	1, '14:36:03.030', 4
	UNION SELECT	1, '14:58:34.613', 6
	UNION SELECT	1, '15:10:20.913', 4
	UNION SELECT	1, '15:15:07.693', 6
	UNION SELECT	1, '15:24:10.900', 4
	UNION SELECT	1, '15:24:13.530', 6
	UNION SELECT	1, '15:24:18.000', 7
	UNION SELECT	2, '15:21:31.343', 1
	UNION SELECT	2, '15:52:08.560', 4
	UNION SELECT	2, '16:09:44.340', 6
	UNION SELECT	2, '16:48:47.423', 7
	)

INSERT	@TBL
SELECT	*
FROM	DAT

--@
SELECT	*,
	ROW_NUMBER() OVER(PARTITION BY CDNO ORDER BY TMDT) AS WORKNO,
	FIRST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT) AS FIRSTNO,
	LAST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT) AS LASTNO
FROM	@TBL

--A
SELECT	*,
	ROW_NUMBER() OVER(PARTITION BY CDNO ORDER BY TMDT) AS WORKNO,
	FIRST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT
	ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS FIRSTNO,
	LAST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT
	ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS LASTNO
FROM	@TBL


@の場合FIRSTNO が PARTITION ないの最初の値[1] になっているのですが結果は

CDNO	TMDT			STC	WORKNO	FIRSTNO	LASTNO
1	1900-01-01 13:47:35.257	1	1	1	1
1	1900-01-01 14:09:27.983	4	2	1	4
1	1900-01-01 14:11:40.020	6	3	1	6
1	1900-01-01 14:36:03.030	4	4	1	4
1	1900-01-01 14:58:34.613	6	5	1	6
1	1900-01-01 15:10:20.913	4	6	1	4
1	1900-01-01 15:15:07.693	6	7	1	6
1	1900-01-01 15:24:10.900	4	8	1	4
1	1900-01-01 15:24:13.530	6	9	1	6
1	1900-01-01 15:24:18.000	7	10	1	7
2	1900-01-01 15:21:31.343	1	1	1	1
2	1900-01-01 15:52:08.560	4	2	1	4
2	1900-01-01 16:09:44.340	6	3	1	6
2	1900-01-01 16:48:47.423	7	4	1	7

LASTNO が[7]になっていません。

そこで、以下を参照して
https://ameblo.jp/nika-tv-2/entry-11528366734.html
>(RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを記述することで、
>LAST_VALUEの値がPrice列の最後の値(2000)になると思います。)
と記述されているのでAのようにしましたが結果は

CDNO	TMDT			STC	WORKNO	FIRSTNO	LASTNO
1	1900-01-01 13:47:35.257	1	1	1	6
1	1900-01-01 14:09:27.983	4	2	4	4
1	1900-01-01 14:11:40.020	6	3	6	6
1	1900-01-01 14:36:03.030	4	4	4	4
1	1900-01-01 14:58:34.613	6	5	6	6
1	1900-01-01 15:10:20.913	4	6	4	4
1	1900-01-01 15:15:07.693	6	7	6	6
1	1900-01-01 15:24:10.900	4	8	4	7
1	1900-01-01 15:24:13.530	6	9	6	7
1	1900-01-01 15:24:18.000	7	10	7	7
2	1900-01-01 15:21:31.343	1	1	1	6
2	1900-01-01 15:52:08.560	4	2	4	7
2	1900-01-01 16:09:44.340	6	3	6	7
2	1900-01-01 16:48:47.423	7	4	7	7

今度はFIRSTNOもLASTNOも期待した値ではなくなりました。
望む FIRSTNO, LASTNO は以下の通りになります。

CDNO	TMDT			STC	WORKNO	FIRSTNO		LASTNO
						同じ CDNO 内の	同じ CDNO 内の
						STC最小値	STC最大値
1	1900-01-01 13:47:35.257	1	1	1		7
1	1900-01-01 14:09:27.983	4	2	1		7
1	1900-01-01 14:11:40.020	6	3	1		7
1	1900-01-01 14:36:03.030	4	4	1		7
1	1900-01-01 14:58:34.613	6	5	1		7
1	1900-01-01 15:10:20.913	4	6	1		7
1	1900-01-01 15:15:07.693	6	7	1		7
1	1900-01-01 15:24:10.900	4	8	1		7
1	1900-01-01 15:24:13.530	6	9	1		7
1	1900-01-01 15:24:18.000	7	10	1		7

2	1900-01-01 15:21:31.343	1	1	1		7
2	1900-01-01 15:52:08.560	4	2	1		7
2	1900-01-01 16:09:44.340	6	3	1		7
2	1900-01-01 16:48:47.423	7	4	1		7

どうすればよいのでしょうか?























引用返信 編集キー/
■95005 / inTopicNo.2)  Re[1]: SQLServer の OVER の設定方法
□投稿者/ ファイター (6回)-(2020/06/11(Thu) 09:06:38)
よくよく考えれば、
LAST_VALUE は使えないんだから
以下のようにすればいいんですよね

SELECT *,
ROW_NUMBER() OVER(PARTITION BY CDNO ORDER BY TMDT) AS WORKNO,
FIRST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT) AS FIRSTNO,
FIRST_VALUE(STC) OVER(PARTITION BY CDNO ORDER BY TMDT DESC) AS LASTNO
FROM @TBL



解決済み
引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ