金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 开窗函数,透视数据

开窗函数,透视数据

发布时间:2019-11-01 09:43编辑:数据库浏览(105)

    昨天一台SQL Server 2008R2的数据库在凌晨5点多抛出下面告警信息:

    1.基本概念

    透视数据实际上就是行状态转为例状态

     

    开窗函数分为两个部分分别是

    先加一张测试表

     The log scan number (620023:3702:1) passed to log scan in database 'xxxx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

    1.聚合,排名,偏移,分布函数 。

    IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
    GO
    
    CREATE TABLE dbo.Orders
    (
      orderid   INT        NOT NULL,
      orderdate DATE       NOT NULL,
      empid     INT        NOT NULL,
      custid    VARCHAR(5) NOT NULL,
      qty       INT        NOT NULL,
      CONSTRAINT PK_Orders PRIMARY KEY(orderid)
    );
    
    INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
    VALUES
      (30001, '20070802', 3, 'A', 10),
      (10001, '20071224', 2, 'A', 12),
      (10005, '20071224', 1, 'B', 20),
      (40001, '20080109', 2, 'A', 40),
      (10006, '20080118', 1, 'C', 14),
      (20001, '20080212', 2, 'B', 12),
      (40005, '20090212', 3, 'A', 10),
      (20002, '20090216', 1, 'C', 20),
      (30003, '20090418', 2, 'B', 15),
      (30004, '20070418', 3, 'C', 22),
      (30007, '20090907', 3, 'D', 30);
    
    SELECT * FROM dbo.Orders;
    

     

    2.开窗分区,排序,框架。

    把这张表查出来

     

    下面举个例子

    SELECT empid, custid, SUM(qty) AS sumqty
    FROM dbo.Orders
    GROUP BY empid, custid;
    

       乍一看,还以为数据库损坏了(data corruption),但是在做完DBCC CHECKDB后,发现其实数据库其实是完好无损的。那么肯定是跟Replication有关。但是在搜索了相关资料,仅仅在The process could not execute ‘sp_repldone/sp_replcounters” 这篇博客中找到了类似错误的资料:

    SELECT empid, ordermonth, val,
      SUM(val) OVER(PARTITION BY empid
                    ORDER BY ordermonth
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runval
    FROM Sales.EmpOrders;
    

    图片 1

     

    sum(val)  就是集合函数

    我们将 custid行转换成例 

    Common Causes

     

    • The last LSN in Transaction Log is less than what the LSN Log Reader is trying to find. An old backup may have been restored on top of Published Database. After the restore, the new Transaction Log doesn't contain the data now distributor & subscriber(s) have.

    • Database corruption.

     

    over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY ordermonth 开窗排序  

    SELECT empid,
    SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
    SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
    SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
    SUM(CASE WHEN custid = 'D' THEN qty END) AS D 
    FROM dbo.Orders
    GROUP BY empid;
    
    How to fix this

     

    • Ensure database consistency by running DBCC CHECKDB on the database. 

    • If an old backup was restored on top of published database then use sp_replrestart

    • If going back to the most recent transaction log backup is not an option then execute sp_replrestart  on publisher in published database. This stored procedure is used when the highest log sequence number (LSN) value at the Distributor does match the highest LSN value at the Publisher.

    • This stored procedure will insert compensating LSNs (No Operation) in the publisher database log file till one the compensating LSN becomes more than the highest distributed LSN in distribution database for this published database. After this it inserts this new high LSN in the msrepl_transactions table in the distribution database and executes sp_repldone on published database to update the internal structures to mark a new starting point for log reader agent.

    • Ensure that the log reader agent is stopped and there is no incoming transactions on the published database, when this SP is executed.

    • Since transactions may have been lost, we recommend to reinitialize the subscriber(s) and/or recreate publication/subscription(s).  For large databases consider using “Initialize from Backup” as discussed in SQL Book Online.

     

    但是在这个案例当中, 数据库既没有损坏,也没有还原过。 只能是Replication出现了错误,但是在SQL Server的Replication中又没有找到相关错误信息,本身这个是AWS的DMS自动生成的Replication,很多内部信息不太清楚(例如,是否出现异常),官方也没有找到很详细的介绍这个错误的相关资料。在此记录一下。

     

     

     

     

    参考资料:

     

     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

    图片 2

    2.排名开窗函数

    sql server 还支持一个子句用于 行转列 PIVOT  是以FROM内嵌 表表达式实现的

     SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   来看看它们分别的作用

    SELECT empid, A, B, C, D
    FROM (SELECT empid, custid, qty
          FROM dbo.Orders) AS D
      PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
    

    本文由金沙国际官网发布于数据库,转载请注明出处:开窗函数,透视数据

    关键词:

上一篇:没有了

下一篇:没有了