久久久久久亚洲精品不卡,在线亚洲成人

人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

公司動態(tài)

產品資訊

行業(yè)資訊

：

今天在優(yōu)化工作中遇到的sql慢的問題，發(fā)現(xiàn)以前用了挺多游標來處理數(shù)據(jù)，這樣就導致在數(shù)據(jù)量多的情況下，需要一行一行去遍歷從而計算需要的數(shù)據(jù)，這樣處理的結果就是數(shù)據(jù)慢，容易卡死。

語法介紹：

1、與Row_Number() 函數(shù)結合使用，對結果進行排序，這個是我們使用的非常多的

2、與聚合函數(shù)結合使用，利用over子句的分組和排序，對需要的數(shù)據(jù)進行操作

例如：SUM() Over() 累加值、AVG() Over() 平均數(shù)
MAX() Over() 最大值、MIN() Over() 最小值

具體介紹：

下面模擬工作中通過開窗函數(shù)代替游標的例子，通過期初余額與單據(jù)的預收金額、應收金額、實收金額來計算截止本單的期末余額，在以往就是通過游標一行一行去遍歷，計算需要的期末余額，現(xiàn)在使用SUM() Over()來代替，最終要實現(xiàn)的效果圖如下：

第一行表示標題；第二行表示客戶，是一行空行；第三行是期初余額，只顯示期末余額的數(shù)據(jù)，第四至第六行表示的是每種單據(jù)的余額情況，并逐步匯總當前行的期末余額數(shù)據(jù)；最后一行表示的是對客戶的合計。

1、構建需要用到的表和數(shù)據(jù)（簡略版）

--客戶表
CREATE TABLE Organization(
 FItemID  INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FNumber  NVARCHAR(255),
 FName  NVARCHAR(255)
)
 
--期初數(shù)據(jù)表
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)  --實收金額
)
 
--單據(jù)明細表
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','銷售發(fā)貨','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
--建立臨時表處理獲取數(shù)據(jù)
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
   --表示客戶已經(jīng)變了，要插入小計
   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 
 
 --插入單據(jù)明細
 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

代碼說明：創(chuàng)建了一個臨時表，使用游標遍歷我們的DetailData數(shù)據(jù)表，為了呈現(xiàn)我們最終需要的數(shù)據(jù)樣式，插入客戶空行、期初余額、單據(jù)信息、客戶小計等，逐行計算期末余額值的情況，最終效果如下：

3、使用SUM() Over()的寫法

SET NOCOUNT ON
--建立臨時表處理獲取數(shù)據(jù)
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
 
--插入單據(jù)明細(關鍵代碼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是累加計算，計算應收金額 - 預收金額 - 實收金額（第二行計算出來的結果要加上第一行計算出來的結果，第三行計算出來的結果要加上第二行計算出來的結果，依次類推，所以，其他聚合函數(shù)也是這種用法哦），PARTITION BY分組統(tǒng)計客戶，并通過Order by指定排序
這個PARTITION BY和Order By結果的用法就很關鍵了，不然計算就不是預期想要的
再舉個例子：比如使用Count() Over() 計算客戶的訂單號

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

總結：

1、游標的使用場景可以很廣，但是在數(shù)據(jù)量大的時候，就會顯得很慢，一行一行遍歷的速度還是挺久的

2、使用開窗函數(shù)來實現(xiàn)一些功能，還是很方便能實現(xiàn)效果，并且它的速度也是很快，值得推薦。

到此這篇關于SQL Server 開窗函數(shù) Over()代替游標的使用的文章就介紹到這了,更多相關SQL Server 開窗函數(shù) Over()內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！

版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。

mysql學習筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個mysql實例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結

一次Mysql死鎖排查過程的全紀錄

Windows10 64位安裝MySQL5.6.35的圖文教程