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

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

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

SQLServer でのデータの取り方を教えてください。

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

■94935 / inTopicNo.1)  SQLServer でのデータの取り方を教えてください。
  
□投稿者/ ファイター (2回)-(2020/06/04(Thu) 15:07:58)

分類:[.NET 全般] 

2020/06/04(Thu) 16:01:38 編集(投稿者)
2020/06/04(Thu) 16:00:45 編集(投稿者)

<pre><pre>SQLServer を使用しています。

Name Code STime ETime MINUTES
name1 4 2020-04-13 11:20:49.153 2020-04-13 12:09:10.733 49
name1 7 2020-04-13 12:53:43.697 2020-04-13 13:37:59.773 44
name2 4 2020-04-09 12:04:06.270 2020-04-09 12:09:18.353 5
name2 4 2020-04-09 12:53:51.683 2020-04-09 13:04:03.547 11
name2 7 2020-04-13 11:13:15.313 2020-04-13 11:20:48.140 7
name3 4 2020-04-20 15:05:56.470 2020-04-20 15:32:07.353 27

Name別に、STime順で並べて以下のようにしたいのですが
同一Nameの開始時間(Stime)が最終時間の時の Code です。
MINUTES STime, ETime の差(分)で同一Nameで合計します。

name1 7 2020-04-13 12:53:43.697 2020-04-13 13:37:59.773 93
name2 7 2020-04-13 11:13:15.313 2020-04-13 11:20:48.140 23
name3 4 2020-04-20 15:05:56.470 2020-04-20 15:32:07.353 27

MINUTES は Group を使って SUM をすれば取得できると思うのですが
Code を取得する方法がわかりません。
</pre></pre>
引用返信 編集キー/
■94945 / inTopicNo.2)  Re[1]: SQLServer でのデータの取り方を教えてください。
□投稿者/ KOZ (126回)-(2020/06/05(Fri) 10:04:31)
No94935 (ファイター さん) に返信
> 同一Nameの開始時間(Stime)が最終時間の時の Code です。

Name が同一で重複する STime が無いとしたとき、こういう場合のアプローチとしては

(1) Name が同一で STime に大きいものが無いレコードを抜き出す

SELECT
    Name
,   Code
,   STime
,   ETime
,   (
        SELECT
            SUM(T2.MINUTES)
        FROM
            Table_1 T2
        WHERE
            T2.Name =   T1.Name
    )
FROM
    Table_1 T1
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            Table_1 T3
        WHERE
            T3.Name     =   T1.Name
        AND T3.STime    >   T1.STime
    )

(2) Name でグルーピング

SELECT
    Name
,   (
        SELECT
            Code
        FROM
            Table_1             T2
        WHERE
            T2.Name                 =   T1.Name
        AND T2.STime                =   MAX(T1.STime)
    )                   Code
,   MAX(T1.STime)       STime
,   MAX(T1.ETime)       ETime
,   SUM(T1.MINUTES)     MINUTES
FROM
    Table_1 T1
GROUP BY
    Name


が考えられます。

STime が重複する場合、(1) のパターンは使えないのでストアドでぶん回すとか
(2) のパターンで Min(Code) か Max(Code) とすればよいかと。



引用返信 編集キー/
■94969 / inTopicNo.3)  Re[1]: SQLServer でのデータの取り方を教えてください。
□投稿者/ YuO (2回)-(2020/06/07(Sun) 15:14:32)
No94935 (ファイター さん) に返信
> Name別に、STime順で並べて以下のようにしたいのですが
> 同一Nameの開始時間(Stime)が最終時間の時の Code です。
> MINUTES STime, ETime の差(分)で同一Nameで合計します。

Window関数を使うのがわかりやすいかな、と思います。

まず、提示されたデータ

DECLARE @table TABLE (
  [Name] VARCHAR(8) NOT NULL,
  [Code] INT NOT NULL,
  [STime] DATETIME2 NOT NULL,
  [ETime] DATETIME2 NOT NULL,
  [MINUTES] INT NOT NULL
);

INSERT INTO @table ( [Name], [Code], [STime], [ETime], [MINUTES] ) VALUES
  ( 'name1', 4, '2020-04-13 11:20:49.153', '2020-04-13 12:09:10.733', 49 ),
  ( 'name1', 7, '2020-04-13 12:53:43.697', '2020-04-13 13:37:59.773', 44 ),
  ( 'name2', 4, '2020-04-09 12:04:06.270', '2020-04-09 12:09:18.353', 5 ),
  ( 'name2', 4, '2020-04-09 12:53:51.683', '2020-04-09 13:04:03.547', 11 ),
  ( 'name2', 7, '2020-04-13 11:13:15.313', '2020-04-13 11:20:48.140', 7 ),
  ( 'name3', 4, '2020-04-20 15:05:56.470', '2020-04-20 15:32:07.353', 27 )
;

に対して、
・[Name]ごとの[MINUTES]の合計
・[Name]ごとの[STime]逆順に並べた場合の行番号
を取得します。

SELECT
  [Name], [Code], [STime], [ETime],
  SUM([MINUTES]) OVER ( PARTITION BY [Name] ) [SUM_MINUTES], -- [Name]単位で[MINUTES]をSUM
  ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY [STime] DESC ) [ROWNUM] -- [Name]単位で[STime]逆順に並べた時のROW_NUMBER
FROM @table;

とすると、

Name     Code        STime                       ETime                       SUM_MINUTES ROWNUM
-------- ----------- --------------------------- --------------------------- ----------- --------------------
name1    7           2020-04-13 12:53:43.6970000 2020-04-13 13:37:59.7730000 93          1
name1    4           2020-04-13 11:20:49.1530000 2020-04-13 12:09:10.7330000 93          2
name2    7           2020-04-13 11:13:15.3130000 2020-04-13 11:20:48.1400000 23          1
name2    4           2020-04-09 12:53:51.6830000 2020-04-09 13:04:03.5470000 23          2
name2    4           2020-04-09 12:04:06.2700000 2020-04-09 12:09:18.3530000 23          3
name3    4           2020-04-20 15:05:56.4700000 2020-04-20 15:32:07.3530000 27          1

が得られます。
あとは必要なのがROWNUM = 1のものだけなので、先のSQLをWITHに放り込むかサブクエリにしてやれば出来上がりです。

WITH [TARGET] AS (
  SELECT
    [Name], [Code], [STime], [ETime],
    SUM([MINUTES]) OVER ( PARTITION BY [Name] ) [SUM_MINUTES],
    ROW_NUMBER() OVER ( PARTITION BY [Name] ORDER BY [STime] DESC ) [ROWNUM]
  FROM @table
)
SELECT
  [Name], [Code], [STime], [ETime], [SUM_MINUTES] [MINUTES]
FROM
  [TARGET]
WHERE
  [ROWNUM] = 1
ORDER BY
  [Name];

Name     Code        STime                       ETime                       MINUTES
-------- ----------- --------------------------- --------------------------- -----------
name1    7           2020-04-13 12:53:43.6970000 2020-04-13 13:37:59.7730000 93
name2    7           2020-04-13 11:13:15.3130000 2020-04-13 11:20:48.1400000 23
name3    4           2020-04-20 15:05:56.4700000 2020-04-20 15:32:07.3530000 27

引用返信 編集キー/
■95003 / inTopicNo.4)  Re[2]: SQLServer でのデータの取り方を教えてください。
□投稿者/ ファイター (4回)-(2020/06/10(Wed) 16:53:08)
No94945 (KOZ さん) に返信
No94969 (YuO さん) に返信

ありがとうございました。
サブクエリにしたのですが、データ数が多くてなかなか戻ってきませんでした。
いろいろ調べてみたのですが、

FIRST_VALUE,LAST_VALUE というのを使うとうまくいくみたいなので
試してみたいと思います。

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

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


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

このトピックに書きこむ