你的位置:首页 > 数据库

[数据库]由最近修改的一个报表想到的


    最近接到新的需求,要求对一个很古老的报表(酒店销售报表hotel sales report)进行修改和优化,原来这个报表包含两种订单来源的订单,HBS和PBS,现在的需求是,一是要分别限制HBS和PBS的订单范围,二是要增加一列,显示订单的取消原因,取消原因分风控过程取消和非风控过程取消。

    第一个需求,分别限制HBS和PBS的订单范围,但是这个限定的逻辑和标准还不太一样,所以我的想法是,创建一个自定义函数function,输入订单号OrderID,具体的逻辑封装在这个函数内,然后这个函数返回一个布尔值就可以。

    对于第二个需求,加一列显示订单取消原因,我的想法是,先把风控过程取消的订单和取消原因拿出来做为第一个集合,然后再把非风控过程取消的订单和取消原因取出来作为第二个集合,再将这两个集合UNION ALL,得到所有取消订单及取消原因的集合,在查询的时候LEFT JOIN这个集合就可以。

    这个报表所关联的订单表数据量非常大,所以查询时的性能问题也必须得考虑。

函数的使用

当我们要封装一段业务逻辑,并返回一个结果的时候,可以选择使用函数。比如我这里的第一个需求,封装了HBS和PBS界定订单范围的逻辑,返回布尔值结果,是否属于这个范围就可以。

函数也分为标量函数和表值函数,标量函数返回一个值,而表值函数返回多个值(或者说一个表)。我这里使用的就是标量函数。如下代码。

USE HotelDB;GO--UDF,判断订单是否成功提交永安--如果是HBS订单,依据OrderID,关联OrderLog表,查询是否有【提交成功】的邮件,如果存在则为true,否则为false--如果是PBS携程订单,只要成功创建永安(本地)订单的都作为‘成功提交永安的订单’。IF OBJECT_ID('dbo.fn_GetIsSuccessSubmitWingon') IS NOT NULL  DROP FUNCTION dbo.fn_GetIsSuccessSubmitWingon;GOCREATE FUNCTION dbo.fn_GetIsSuccessSubmitWingon  (   @OrderID AS INT ,   @CategoryType AS VARCHAR(50)  )RETURNS INTAS  BEGIN    DECLARE @Result AS INT;    DECLARE @IsPBSOrder AS BIT;    DECLARE @PkgOrderNo AS VARCHAR(10);        IF ( @CategoryType NOT IN ( 'HBS', 'PBS' ) )            --是否PBS订单      IF EXISTS ( SELECT *            FROM  dbo.HotelOrder (NOLOCK) AS hotelOrder            WHERE  hotelOrder.OrderID = @OrderID                AND hotelOrder.OrderType NOT IN ( 1, 2, 3, 4,                               5, 11, 12, 13 )                AND hotelOrder.ResourceFromFlagType = 2 )        SET @IsPBSOrder = 1;      ELSE        SET @IsPBSOrder = 0;    ELSE      IF ( @CategoryType = 'PBS' )        SET @IsPBSOrder = 1;      ELSE        SET @IsPBSOrder = 0;         IF ( @IsPBSOrder = 1 )--PBS订单      BEGIN        SET @Result = 1;              --SELECT @PkgOrderNo = orderMapping.MappingID        --FROM  dbo.OrderMapping (NOLOCK) AS orderMapping        --WHERE  orderMapping.HotelOrderID = @OrderID;                --IF EXISTS ( SELECT *        --      FROM  HP580DBSZ.PackageFHDB.dbo.Pkg_OrderLog AS orderLog        --      WHERE  orderLog.PkgOrderNo = @PkgOrderNo        --          AND orderLog.OperationType = 1--成功提交度假订单        --          AND orderLog.Operator = 'System' )        --  SET @Result = 1;        --ELSE        --  SET @Result = 0;                    END;    ELSE      BEGIN        IF EXISTS ( SELECT *              FROM  dbo.HotelOrderLog (NOLOCK) AS orderLog              WHERE  orderLog.OrderID = @OrderID                  AND orderLog.OperationType = 4--发邮件                  AND orderLog.NewOrderStatus = 10                  AND orderLog.Remark LIKE N'【提交成功】%' )          SET @Result = 1;        ELSE          SET @Result = 0;      END;        RETURN @Result;  END;

标量函数一般用在SELECT和WHERE语句中。

使用在SELECT语句中,既可以作为一列,也可以作为条件(一般在CASE WHEN语句中)。

使用在WHERE语句中,只用为条件来使用,如下代码。

AND dbo.fn_GetIsSuccessSubmitWingon(a.OrderID, @CategoryType) > 0--已成功提交永安

到底使用临时表还是表表达式

在实现第二个需求的时候,一开始我使用的是表表达式,因为方便简洁,但是后面发现不行,因为表表达式的生命周期随着第一个外部查询完成就结束了,比如下面这样就不行。

USE HotelDB;GO--CTEWITH NoCardRiskCancelOrders AS( SELECT hotelOrder.OrderID ,      hotelOrder.CreateTime ,      hotelOrder.CancelReason  --INTO  #NoCardRiskCancelOrders  FROM  dbo.HotelOrder (NOLOCK) AS hotelOrder      LEFT JOIN dbo.HotelOrderExtend AS orderExtend ON orderExtend.OrderID = hotelOrder.OrderID  WHERE  hotelOrder.OrderStatus = 40      AND orderExtend.CardRiskStatus IS NULL)--第一个外部查询SELECT TOP 10 * FROM dbo.HotelOrder (NOLOCK) AS hotelOrder;--使用CTE ,报错SELECT * FROM NoCardRiskCancelOrders;

clipboard

因为这个结果集在后面要多次被用到,所以最后我选择了传统的临时表来实现。如下代码。

--2,非风控过程取消订单  SELECT hotelOrder.OrderID ,      hotelOrder.CreateTime ,      hotelOrder.CancelReason  INTO  #NoCardRiskCancelOrders  FROM  dbo.HotelOrder (NOLOCK) AS hotelOrder      LEFT JOIN dbo.HotelOrderExtend AS orderExtend ON orderExtend.OrderID = hotelOrder.OrderID  WHERE  hotelOrder.OrderStatus = 40      AND orderExtend.CardRiskStatus IS NULL      AND ( @BegBookingDate IS NULL         OR hotelOrder.CreateTime >= @BegBookingDate        )      AND ( @EndBookingDate IS NULL         OR hotelOrder.CreateTime < DATEADD(ms, -4,                           DATEADD(d, 1,                               @EndBookingDate))        );--暂时将结果集放到临时表中  SELECT *  INTO  dbo.#t1  FROM  dbo.#CardRiskCancelOrders  UNION ALL  SELECT *  FROM  dbo.#NoCardRiskCancelOrders;

输出的字段有多种情形时使用CASE...WHEN

我们的报表有些字段展示很多时候有多种情形,每个情形显示的结果不一样,一般我们可以在SELECT中使用CASE...WHEN来实现。

比如,这个报表中字段NoShowStatus的展示逻辑是这样的。

CASE WHEN BalanceType = 'PP' THEN ''         WHEN OrderStatusFlag & 2048 = 2048 THEN N'否'         WHEN OrderStatusFlag & 16384 = 16384 THEN N'是'         WHEN OrderStatusFlag & 8192 = 8192 THEN N'複核中'         WHEN OrderStatusFlag & 4096 = 4096 THEN N'未複核'         ELSE N'否'      END NoShowStatus ,

你真的会用联接查询吗

我们一般使用得比较多的是内联接查询(INNER JOIN)和外联接查询(LEFT JOIN)这两种。

内联接查询有两个处理步骤:笛卡尔积运算和过滤。

外联接查询有三个处理步骤:笛卡尔积运算,过滤和添加外部行。

要理解外部行,首先要明白什么是保留行,保留行指的是LEFT关键字左边的那些行,添加外部行,就是保留行中按照ON条件在另一个表找不到与之匹配的那些行,然后把这些行添加到LEFT左边生成的结果表中,需要注意的是,对于来自联接的非保留表的那些列,添加的外部行中的这些列的值为NULL。

对于外联接,如果只是做联接,是不会影响最终输出的记录数的,只有使用在WHERE语句中才会影响最终输出的记录数的。

性能很重要

对于报表来说,性能很重要。

通常我们可以通过以下几个手段来提高性能。

1,缩小查询范围。缩小查询范围相当于减少数据量。比如有查询条件预订日期的,我们可以带上这个条件。

2,合理使用联接查询。有些使用内联接和外联接都可以的情况下,尽量使用内联接,因为外联接会添加外部行,增加数据量。

3,相关字段建立索引,这是比较通常的做法。