SQL Serverオープンウィンドウ関数Over()は、ラベルの代わりに使用されています。

13281 ワード

前言:
今日は仕事の最適化の中で遭遇したsqlの遅い問題について、以前は多くの観光標識を使ってデータを処理していました。このようにデータ量が多い場合、一行が遍歴して必要なデータを計算する必要があります。このように処理した結果、データが遅くて、カードが死にやすいです。
文法紹介：
1、Row_とNumber()関数を組み合わせて使用して、結果を並べ替えます。これは私達が使っているものが非常に多いです。

2、重合関数と組み合わせて使用し、over子文のパケットと並べ替えを利用して、必要なデータを操作する。
例えば、SUM()Over()の積算値、AVG()Over()の平均数
MAX()Over()最大値、MIN()Over()最小値
具体的な紹介：
下記のシミュレーション作業では、ウインドウオープン関数を通じて、旅行標識の代わりに、期首残高と伝票の前受金額、未収金額、実収金額を通じて、本シングルの期末残高を計算します。これまでは旅行ガイドの一行で巡回して、必要な期末残高を計算します。今はSUM（）Over（）を使って代用して、最終的に実現する効果図は以下の通りです。

最初の行はタイトルを表します。第二行は取引先を表しています。一行の空白です。第三行は期首残高で、期末残高のデータだけを表示して、第四行から第六行までは各単項の残高状況を表しています。そして、徐々に現在の行の期末残高データをまとめます。最後の行はお客様の合計を表しています。
1、必要なテーブルとデータを構築する（簡略版）


--   
CREATE TABLE Organization(
 FItemID  INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FNumber  NVARCHAR(255),
 FName  NVARCHAR(255)
)
 
--     
CREATE TABLE InitialData(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FCustId   INT NOT NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --    
)
 
--     
CREATE TABLE DetailData(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FCustId   INT NOT NULL,
 FDate   DATETIME NOT NULL,
 FBillType  NVARCHAR(64) NOT NULL,
 FBillNo   NVARCHAR(64) NOT NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --    
)
 
INSERT INTO Organization(FNumber,FName) VALUES('001','    ')
INSERT INTO Organization(FNumber,FName) VALUES('002','    ')
INSERT INTO Organization(FNumber,FName) VALUES('003','    ')
 
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,0,0,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,8000,7245,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,0,1068.21,1068.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','    ','XSD20200700008',0,1221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','    ','XSD20200700009',0,373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-06-30','      ','XSD20200700010',0,-427.05,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,'2020-07-30','      ','XSFL20200700005',0,-17.9,0)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-25','    ','SKD20200700002',-755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','    ','XSD20200700006',0,6169.50,6169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-30','      ','XSFL20200700002',0,-493.56,-421.85)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-07-31','    ','QTYS20200900001',0,6000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,'2020-06-20','     ','HXD20200700006',-7245.00,0,7245.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','    ','SKD20200700003',0,0,2386.96)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-06-30','     ','HXD20200700007',0,2386.75,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,'2020-07-08','    ','XSD20200700014',0,-46.80,0)
GO

2、従来のラベルの書き方


SET NOCOUNT ON
--           
CREATE TABLE #DATA(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FClassTypeId  INT NOT NULL,
 FCustId   INT NOT NULL,
 FNumber   NVARCHAR(255),
 FName   NVARCHAR(255),
 FDate   DATETIME NULL,
 FBillType  NVARCHAR(64) NULL,
 FBillNo   NVARCHAR(64) NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FBalanceAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)    --    
)
 
Declare @Id     INT
Declare @CustId    INT
Declare @PreAmount   decimal(28,10)
Declare @ReceivableAmount decimal(28,10)
Declare @ReceiveAmount  decimal(28,10)
Declare @OldCustId   int
Declare @Count    int
Declare @LastAmount   decimal(28,10)
Declare @SumPreAmount  decimal(28,10)
Declare @SumReceivableAmount decimal(28,10)
Declare @SumReceiveAmount decimal(28,10)
Declare @SumBalanceAmount decimal(28,10)
 
--    
Declare Data_cursor Cursor
For Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount
 From DetailData
 Order By FCustId,FDate,FID
OPEN Data_cursor
FETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
SET @OldCustId = @CustId
SET @Count = 0
SET @LastAmount = 0
SET @SumPreAmount = 0
SET @SumReceivableAmount = 0
SET @SumReceiveAmount = 0
SET @SumBalanceAmount = 0
WHILE @@FETCH_STATUS = 0
BEGIN 
 IF @Count > 0
 BEGIN
  IF @OldCustId <> @CustId 
  BEGIN
   --        ，     
   SET @Count = 0
   INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
   SELECT -9999,FName + '  ',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
   FROM Organization
   WHERE FItemID = @OldCustId
   Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
  END  
 END 
 IF @Count = 0
 BEGIN
  Set @OldCustId=@CustId
  --      
  INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
  SELECT -1000,FName,FItemID,FNumber,FName
  FROM Organization
  WHERE FItemID = @CustId
 
  --         
  SELECT @LastAmount=isnull(FReceivableAmount,0) - isnull(FPreAmount,0) - isnull(FReceiveAmount,0),@PreAmount=isnull(FPreAmount,0),@ReceivableAmount=isnull(FReceivableAmount,0),@ReceiveAmount=isnull(FReceiveAmount,0)
  FROM InitialData
  WHERE FCustId = @CustId
 
  INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
  VALUES(-1000,'    ',@CustId,'','',@LastAmount)
 
  SELECT @Count = 1
  SELECT @SumBalanceAmount = @LastAmount
 END 
 
 --      
 INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
 SELECT 0,d.FCustId,o.FNumber,o.FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,@LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount
 FROM DetailData d
 INNER JOIN Organization o ON d.FCustId = o.FItemID
 WHERE d.FCustId = @CustId AND FID = @Id
 
 SELECT
 @LastAmount = @LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount,
 @SumPreAmount=@SumPreAmount + FPreAmount,@SumReceivableAmount=@SumReceivableAmount + FReceivableAmount,
 @SumReceiveAmount=@SumReceiveAmount + FReceiveAmount
 FROM DetailData
 WHERE FCustId = @CustId AND FID = @Id
 
 FETCH NEXT FROM Data_cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
END
IF @Count > 0
BEGIN
 INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
 SELECT -9999,FName + '  ',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
 FROM Organization
 WHERE FItemID = @OldCustId
 Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
END
CLOSE Data_cursor
DEALLOCATE Data_cursor
 
SELECT * FROM #DATA
ORDER BY FCustId,FID
 
DROP TABLE #DATA

コードの説明：臨時表を作成しました。旅行標識を使って、私達のDetailDataデータ表を巡回しました。私達が最終的に必要とするデータ様式を表示するために、お客様の空欄、期首残高、単数情報、お客様の小計などを挿入して、行ごとに期末残高値を計算する場合、最終的な効果は以下の通りです。

3、SUM()Over()を使う書き方


SET NOCOUNT ON
--           
CREATE TABLE #DATA(
 FID     INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FClassTypeId  INT NOT NULL,
 FCustId    INT NOT NULL,
 FNumber    NVARCHAR(255),
 FName    NVARCHAR(255),
 FDate    DATETIME NULL,
 FBillType   NVARCHAR(64) NULL,
 FBillNo    NVARCHAR(64) NULL,
 FPreAmount   DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --    
 FBalanceAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --    
)
 
--    
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
SELECT -1000,FName,FItemID,FNumber,FName
FROM Organization o
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--      
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
SELECT -1000,'    ',FItemID,'','',i.FReceivableAmount - i.FPreAmount -i.FReceiveAmount
FROM Organization o
INNER JOIN InitialData i ON o.FItemID = i.FCustId
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--      (    SUM() Over() )
INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT 0,d.FCustId,o.FNumber,o.FName,d.FDate,d.FBillType,d.FBillNo,d.FPreAmount,d.FReceivableAmount,d.FReceiveAmount,
SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)
+ i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM DetailData d WITH(NOLOCK)
INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustId
INNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustId
ORDER BY d.FCustId,d.FDate,d.FID
 
--    
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT -9999,FName + '  ',d.FCustId,FNumber,FName,SUM(FPreAmount),SUM(FReceivableAmount),SUM(FReceiveAmount),0
FROM dbo.DetailData d
INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID
GROUP BY d.FCustId,o.FName,o.FNumber
 
--         
UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM #DATA d
INNER JOIN InitialData i ON d.FCustId = i.FCustId
WHERE d.FClassTypeId = -9999
 
SELECT * FROM #DATA
ORDER BY FCustId,FID
 
DROP TABLE #DATA

コード説明：第二の種類に比べて、ラベルの書き方を消して通過しました。


SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)

私たちが必要とする値を計算します。この文法ではsumは積算して未収金額-前収金額-実収金額を計算します。（2行目で計算した結果は1行目で計算した結果を加えます。3行目で計算した結果は2行目で計算した結果を加えて順に類推します。他の集計関数もこのような使い方です。PARTTION BYグループはお客様を統計し、Order byで順序を指定します。
このPARTTION BYとOrder Byの結果の使い方が重要です。でないと、計算は期待したものではありません。
もう一つの例を挙げます。例えば、Count()Over()を使ってお客様の注文番号を計算します。


SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData

まとめ:
1、ランドマークの使用シーンは広いですが、データ量が多い時は遅くなります。一行が回るスピードはかなり長いです。
2、窓開け関数を使っていくつかの機能を実現します。やはり効果を実現するのに便利です。しかもそのスピードもとても速くて、推薦に値します。
ここでは、SQL Serverオープンウィンドウ関数Over()の代わりに使用されている記事を紹介します。SQL Serverオープンウィンドウ関数Over()の詳細については、以前の記事を検索してください。または下記の関連記事を引き続きご覧ください。これからもよろしくお願いします。

SQL Serverにおけるidentity（自己増加）の使い方の詳細

partition byとgroup byの比較を詳しく説明する。