最新の1件 - sql 最新日付 条件




各グループの上位1行を取得する (11)

私は各グループの最新のエントリーを入手したいテーブルを持っています。 テーブルは次のとおりです:

DocumentStatusLogsテーブル

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

テーブルはDocumentIDでグループ化され、降順でDateCreatedでソートされます。 各DocumentIDについて、私は最新のステータスを取得したい。

私の好みの出力:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |
  • 各グループからトップのみを取得する集約関数はありますか? 以下の擬似コードGetOnlyTheTop参照してください:

    SELECT
      DocumentID,
      GetOnlyTheTop(Status),
      GetOnlyTheTop(DateCreated)
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ORDER BY DateCreated DESC
    
  • そのような機能が存在しない場合、私が望む出力を達成できる方法はありますか?

  • または、最初は非正規化データベースによって引き起こされる可能性がありますか? 私は思っているのは、私が探しているのは単なる行であり、そのstatusも親テーブルにあるはずですから?

詳細については親テーブルを参照してください:

現在のDocumentsテーブル

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

親テーブルをこのようにすれば、私はそのステータスに簡単にアクセスできますか?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

UPDATE私はちょうどそのような問題に対処するのを容易にする "適用"を使用する方法を学びました。


SQLiteでは、 GROUP BYで次の簡単なクエリを使用できるかどうかをチェックします

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

ここでMAXは各グループから最大のDateCreatedを取得するのに役立ちます。

しかし、MYSQLは* -columnsをmax DateCreatedの値に関連付けていないようです。


row_count()の使用を避けたいシナリオでは、左結合を使用することもできます。

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

サンプルのスキーマでは、「副問合せにはない」を使用することもできます。これは、通常、左結合と同じ出力にコンパイルされます。

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

表に少なくとも1つの単一列の一意キー/制約/索引(この場合は主キー "Id")がない場合、副問合せパターンは機能しません。

これらのクエリは両方とも、row_count()クエリ(クエリアナライザで測定)よりも「コストがかかる」傾向があります。 ただし、結果が速くなるか、他の最適化が可能なシナリオが発生する可能性があります。


これはかなり古いスレッドですが、受け入れられた答えが私にとって特にうまくいきませんでしたので、私はちょうど同じ2セントを投げたいと思っていました。 大規模なデータセットでgbnのソリューションを試したところ、SQL Server 2012の5百万件以上のレコードで45秒を超えて非常に遅いことが判明しました。 実行計画を見ると、問題を大幅に遅くするSORT操作が必要であることが明らかです。

ここでは、SORT操作を必要とせず、NON-Clustered Index検索を行うエンティティフレームワークから取り上げた別の方法があります。 これにより、前述のレコードセットで実行時間が<2秒に短縮されます。

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

今私は、元の質問では完全に指定されていないものを想定していますが、IDの列が自動インクリメントIDであり、DateCreatedが各挿入とともに現在の日付に設定されているようなテーブル設計の場合は、上記のクエリでは実行しないと、実際には、DateCreatedで並べ替えるのではなく、IDで並べ替えるだけでgbnのソリューション(約半分の実行時間)に相当なパフォーマンスが得られます。


これはトピックで最も簡単に見つかった質問の1つで、私はそれについて現代的な答えを与えたいと思っていました。 以上の値と最初の値を使用すると、上記のクエリを短期間で処理できます。

select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

これはSQL Server 2008以降で動作するはずです。 最初の値は、over節を使用するときに選択トップ1を達成する方法と考えることができます。 Overはselectリストのグループ化を可能にします(ネストされたサブクエリを作成するのではなく、既存の回答の多くがそうします)。これはより読みやすい方法で行います。 お役に立てれば。


これを試して:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

上記からClintの素晴らしい答えを確認する:

以下の2つのクエリ間のパフォーマンスは面白いです。 52%がトップ1です。 48%が第2のものです。 ORDER BYではなくDISTINCTを使用してパフォーマンスを4%向上させました。 しかし、ORDER BYは複数の列でソートする利点があります。

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

オプション1:

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

オプション2:

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
    OUTER APPLY  (
        SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
        FROM (
            SELECT 
                [Extent2].[ID] AS [ID], 
                [Extent2].[DocumentID] AS [DocumentID], 
                [Extent2].[Status] AS [Status], 
                [Extent2].[DateCreated] AS [DateCreated]
            FROM #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

M $のManagement Studio:最初のブロックを強調表示して実行した後、オプション1とオプション2の両方を強調表示し、右クリック→[推定実行計画の表示]をクリックします。 その後、全体を実行して結果を確認します。

オプション1の結果:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

オプション2結果:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

注意:

私は結合を1対1(1対多)にしたいときにAPPLYを使用する傾向があります。

結合が1対多数または多対多になるようにするには、JOINを使用します。

私は、何か高度な操作を行う必要がなければ、ROW_NUMBER()でCTEを回避し、ウィンドウ処理のパフォーマンス上の不利益を抱えています。

私はまた、WHERE句またはON句でEXISTS / INサブクエリを避けます。これは、これがいくつかのひどい実行計画を引き起こすことを経験しています。 しかし、走行距離は異なります。 必要な場所と実行計画を確認してください。


私はここでのさまざまな推奨事項のいくつかのタイミングを実行しました。結果は実際に関係するテーブルのサイズに依存しますが、最も一貫した解決策はCROSS APPLYを使用しています。これらのテストは、SQL Server 2008- 6,500のレコード、および137,000,000のレコードを持つ別の(同じスキーマ)。 照会される列は、表の主キーの一部であり、表の幅は非常に小さい(約30バイト)。 時刻はSQL Serverによって実際の実行計画から報告されます。

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

本当に驚くべきことは、関係する行の数に関係なく、CROSS APPLYの時間がどれくらい一貫していたかということでした。


私はこれが古いスレッドであることを知っていますが、 TOP 1 WITH TIESソリューションはかなりいいですし、解決策を読んでいく上で参考になるかもしれません。

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

TOP句の詳細はhere


;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

1日に2つのエントリがあるとすると、これは任意に1つを選択します。 1日の両方のエントリを取得するには、代わりにDENSE_RANKを使用します。

正規化されているかどうかは、

  • 2か所でステータスを維持する
  • ステータス履歴を保持する
  • ...

それが現れても、ステータス履歴は保持されます。 親テーブルに最新のステータス(非正規化)も必要な場合は、親に「ステータス」を維持するトリガーが必要です。 またはこの状態履歴表をドロップします。


SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

どのデータベースサーバーですか? このコードはすべてでは機能しません。

あなたの質問の後半については、ステータスを列として含めるのが妥当と思われます。 DocumentStatusLogsをログとして残しても、メインテーブルに最新の情報を保存することができます。

ところで、すでにDocumentテーブルにDateCreatedカラムがある場合、 DocumentStatusLogsを使用してDocumentStatusLogs参加できます( DocumentStatusLogsではDateCreatedが一意である限り)。

編集:MsSQLはUSINGをサポートしていませんので、変更してください:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

DateCreatedによって最近のドキュメントの注文のみを返却したい場合は、DocumentIDで上位1つのドキュメントのみを返します







greatest-n-per-group