2017年10月26日星期四07:45

使用SQL临时表可以解决的5个业务问题

撰写者

星期五下午4:30,经理先生来告诉您,他需要您为他进行一些重要的临时分析。

以前,这意味着必须在办公室待到很晚,编写繁琐的查询才能从交易数据中提取业务信息。

幸运的是,您最近开始在SQL Server中使用临时表,以确保您能够回答老板的问题,并且仍然可以花3美元玛格丽塔酒度过欢乐时光。

听起来像计划吗?继续阅读下面的内容!

数据

对于这些演示,我们将使用我想象中的租车业务数据。它由我们的时态表dbo.CarInventory和历史表dbo.CarInventoryHistory组成:

瓦格纳102617 a

我已经升级了业务—我们现在有四个雪佛兰Malibus供您租用

业务问题1-“获取最新库存!”

要获取当前的租车库存,我们要​​做的就是查询临时表:

选择*从dbo.CarInventory

而已。

我知道这个查询似乎很la脚- 从中选择 声明。没有 对于系统时间 条款 哪里 语句,没有其他有趣的T-SQL功能。

但这就是重点!您是否曾经不得不从跟踪所有事务的表中获取“当前”行?我确定它涉及一些GROUP BY语句,一些窗口函数以及多杯咖啡。

临时表自动管理您的交易历史记录,在一个表(dbo.CarInventory)中提供最新记录,在另一个表(dbo.CarInventoryHistory)中提供所有历史记录。无需复杂的查询。

业务问题2-“我们的客户平均每辆车行驶多少英里?”

在这个例子中,我们使用 FOR SYSTEM_TIME全部 和 a plain old 通过...分组 获取我们需要的数据:

选择
   CarId,AVG(里程)AS平均里程

   dbo.CarInventory FOR SYSTEM_TIME全部
哪里
   InLot = 1-汽车已成功退还给我们
    AND SysStartTime >'2017-05-13 08:00:00.0000000'-忽略我们的首次购车
通过...分组
    CarId

瓦格纳102617羽

有些汽车开得多。是因果还是相关?

FOR SYSTEM_TIME全部 返回时间和历史表中的所有行。等同于:

选择*从dbo.CarInventory 
全联盟  
选择*来自dbo.CarInventoryHistory

再一次,这里没有什么花哨的事情-但这就是重点。使用时态表,可以对数据进行组织以使分析更加容易。

业务问题3-“每周我们要租多少辆车?”

在Wagner Car Rentals,我们想弄清楚我们租车的频率,并查看这些数字每周之间的变化。

选择
    当前周刊 .CarId,
   CurrentWeek.RentalCount 如 CurrentRentalCount,
   PreviousWeek.RentalCount 如 PreviousRentalCount

    (
    选择
      CarId,
     COUNT(*)个AS RentalCount
    从
     dbo.CarInventory FOR SYSTEM_TIME从“ 2017-06-05”到“ 2017-06-12”
    哪里
     InLot = 0-顾客出车
    通过...分组
      CarId
    ) 当前周刊
    FULL JOIN
    (
    选择
      CarId,
     COUNT(*)个AS RentalCount
    从
     dbo.CarInventory FOR SYSTEM_TIME从“ 2017-05-29”到“ 2017-06-05”
    哪里
     InLot = 0-顾客出车
    通过...分组
      CarId
    ) PreviousWeek
   开启CurrentWeek.CarId = PreviousWeek.CarId

瓦格纳102617 c


广告

在此查询中,我们正在使用 FOR SYSTEM_TIME FOR / TO 在时间表上指定要在“ 当前周刊 ”和“ PreviousWeek”子查询中使用的数据。

FOR / TO 返回指定范围内处于活动状态的所有记录(BETWEEN / AND执行相同的操作,但其上限datetime2值是包含值而不是排他性值)。

业务问题4-“最常租用哪种颜色的汽车?”

我们正在考虑扩大租赁车辆的数量,并希望以最受欢迎的颜色购买汽车,以使客户满意(并获得更多业务!)。我们如何知道哪种颜色的汽车最常租用?

选择
    CarId,
    Color,
   COUNT(*)/ 2 如 RentalCount-除以2是因为交易是重复计算的(租赁和退货日期)

   dbo.CarInventory FOR SYSTEM_TIME 包含在('2017-05-15','2017-06-15')
通过...分组
    CarId,
    Color

在这里我们用 包含在 因为我们想精确地计算出在特定日期范围内租用和归还的汽车数量(如果未归还汽车,-被盗,毁坏和总计……等)-我们不想购买更多的颜色未来)。

瓦格纳102617 d

业务问题5 –杰里打破了难题。修理它!”

我们在Wagner租车公司使用的计算机系统有些陈旧。

我们的员工无需手动扫描条形码即可将汽车返回我们的系统,而需要手动输入汽车详细信息。这里的问题是某些员工(例如Jerry)无法打字,而且经常会打错字:

选择*从dbo.CarInventory FOR SYSTEM_TIME凡CarId = 4

瓦格纳102617 e

数据不一致会使我们的报告变得更加困难,但是幸运的是,由于我们具有时态表跟踪行级历史记录,因此可以通过从以前的记录中提取正确的值来轻松地纠正Jerry的错别字:

;与库存历史记录

(
   SELECT ROW_NUMBER()超过(按CarId排序或按SysStartTime DESC排序)作为RN,*
   从dbo.CarInventory FOR SYSTEM_TIME,所有CarId = 4
)
-选择*来自库存历史
/ *通过使用历史记录中的第N行版本来更新当前行(默认值为1-即最新版本)* /
更新dbo.CarInventory
    SET Color = h.Color
    从
      dbo.CarInventory i
     内部联接库存历史h
       开启i.CarId = h.CarId
        AND RN = 2

瓦格纳102617 f

错字修复!

尽管我们可以在不使用临时表的情况下解决此问题,但是它显示了拥有所有行级事务历史记录如何在更困难的情况下修复错误的数据。

结论

临时表易于设置,并且使编写分析查询变得轻松。

希望针对时态表编写查询可以避免您下次经理要求您进行临时分析时,不必在办公室待到很晚。

伯特·瓦格纳

SQL Server业务分析师培训: //blog.bertwagner.com

©BA Times.com 2020

麦格雷戈徽标白色网站