您当前的位置:首页 > 电脑百科 > 数据库 > 百科

五个最难的SQL任务:数据缺失、透视、独热编码

时间:2023-09-20 13:36:25  来源:微信公众号  作者:Java学研大本营

许多人体验到了云数据仓库内集中计算的速度和效率优势,但同时大家也认识到这种方法存在一些缺点。缺点之一是需要学习和执行不同语言(尤其是SQL)的查询,这增加了很多复杂性。

为了解决这个问题,本文分享一些最难以在SQL中学习和执行的具体转换,并提供实际所需的SQL代码。

1. Datespine

Datespine是生成日期索引的转换。

想象下,您正在分析每日销售数据,表格如下:

因16日和17日没有销售,对应的行缺失。如果想计算平均每日销售额或构建时间序列预测模型,那这种格式是个问题,我们需要插入缺失日期的行。

基本概念:

  • 生成或选择唯一日期
  • 生成或选择唯一产品
  • 交叉连接(笛卡尔积)1&2的所有组合
  • 将第3步的结果与原始数据进行外连接
WITH GLOBAL_SPINE AS (
  SELECT 
    ROW_NUMBER() OVER (
      ORDER BY 
        NULL
    ) as INTERVAL_ID, 
    DATEADD(
      'day', 
      (INTERVAL_ID - 1), 
      '2020-01-01T00:00' :: timestamp_ntz
    ) as SPINE_START, 
    DATEADD(
      'day', INTERVAL_ID, '2020-01-01T00:00' :: timestamp_ntz
    ) as SPINE_END 
  FROM 
    TABLE (
      GENERATOR(ROWCOUNT => 1097)
    )
), 
GROUPS AS (
  SELECT 
    product, 
    MIN(sales_date) AS LOCAL_START, 
    MAX(sales_date) AS LOCAL_END 
  FROM 
    My_First_Table 
  GROUP BY 
    product
), 
GROUP_SPINE AS (
  SELECT 
    product, 
    SPINE_START AS GROUP_START, 
    SPINE_END AS GROUP_END 
  FROM 
    GROUPS G CROSS 
    JOIN LATERAL (
      SELECT 
        SPINE_START, 
        SPINE_END 
      FROM 
        GLOBAL_SPINE S 
      WHERE 
        S.SPINE_START >= G.LOCAL_START
    )
) 
SELECT 
  G.product AS GROUP_BY_product, 
  GROUP_START, 
  GROUP_END, 
  T.* 
FROM 
  GROUP_SPINE G 
  LEFT JOIN My_First_Table T ON sales_date >= G.GROUP_START 
  AND sales_date < G.GROUP_END 
  AND G.product = T.product;

最终结果如下所示:

2. Pivot

Pivot是一种将行数据转换为列数据的操作,以便更好地进行分析和可视化。

有时,在进行分析时,您希望重新构造表格。例如,有个罗列学生、科目和成绩表格,我们想把具体科目分解为每个列。

之前:

SELECT Student, MATHEMATICS, GEOGRAPHY, PHYS_ED
FROM ( SELECT Student, Grade, Subject FROM skool)
PIVOT ( AVG ( Grade ) FOR Subject IN ( 'Mathematics', 'Geography', 'Phys Ed' ) ) as p
( Student, MATHEMATICS, GEOGRAPHY, PHYS_ED );

结果:

3. One-Hot编码(或“虚拟”变量)

One-Hot编码是一种将分类变量转换为数字变量的方法。

这里是一个使用STATE作为列进行独热编码的示例。

之前:

SELECT *,
    CASE WHEN State = 'AL' THEN 1 ELSE 0 END as STATE_AL, 
    CASE WHEN State = 'AK' THEN 1 ELSE 0 END as STATE_AK, 
    CASE WHEN State = 'AZ' THEN 1 ELSE 0 END as STATE_AZ, 
    CASE WHEN State = 'AR' THEN 1 ELSE 0 END as STATE_AR, 
    CASE WHEN State = 'AS' THEN 1 ELSE 0 END as STATE_AS, 
    CASE WHEN State = 'CA' THEN 1 ELSE 0 END as STATE_CA, 
    CASE WHEN State = 'CO' THEN 1 ELSE 0 END as STATE_CO, 
    CASE WHEN State = 'CT' THEN 1 ELSE 0 END as STATE_CT, 
    CASE WHEN State = 'DC' THEN 1 ELSE 0 END as STATE_DC, 
    CASE WHEN State = 'FL' THEN 1 ELSE 0 END as STATE_FL, 
    CASE WHEN State = 'GA' THEN 1 ELSE 0 END as STATE_GA, 
    CASE WHEN State = 'HI' THEN 1 ELSE 0 END as STATE_HI, 
    CASE WHEN State = 'ID' THEN 1 ELSE 0 END as STATE_ID, 
    CASE WHEN State = 'IL' THEN 1 ELSE 0 END as STATE_IL, 
    CASE WHEN State = 'IN' THEN 1 ELSE 0 END as STATE_IN, 
    CASE WHEN State = 'IA' THEN 1 ELSE 0 END as STATE_IA, 
    CASE WHEN State = 'KS' THEN 1 ELSE 0 END as STATE_KS, 
    CASE WHEN State = 'KY' THEN 1 ELSE 0 END as STATE_KY, 
    CASE WHEN State = 'LA' THEN 1 ELSE 0 END as STATE_LA, 
    CASE WHEN State = 'ME' THEN 1 ELSE 0 END as STATE_ME, 
    CASE WHEN State = 'MD' THEN 1 ELSE 0 END as STATE_MD, 
    CASE WHEN State = 'MA' THEN 1 ELSE 0 END as STATE_MA, 
    CASE WHEN State = 'MI' THEN 1 ELSE 0 END as STATE_MI, 
    CASE WHEN State = 'MN' THEN 1 ELSE 0 END as STATE_MN, 
    CASE WHEN State = 'MS' THEN 1 ELSE 0 END as STATE_MS, 
    CASE WHEN State = 'MO' THEN 1 ELSE 0 END as STATE_MO, 
    CASE WHEN State = 'MT' THEN 1 ELSE 0 END as STATE_MT, 
    CASE WHEN State = 'NE' THEN 1 ELSE 0 END as STATE_NE, 
    CASE WHEN State = 'NV' THEN 1 ELSE 0 END as STATE_NV, 
    CASE WHEN State = 'NH' THEN 1 ELSE 0 END as STATE_NH, 
    CASE WHEN State = 'NJ' THEN 1 ELSE 0 END as STATE_NJ, 
    CASE WHEN State = 'NM' THEN 1 ELSE 0 END as STATE_NM, 
    CASE WHEN State = 'NY' THEN 1 ELSE 0 END as STATE_NY, 
    CASE WHEN State = 'NC' THEN 1 ELSE 0 END as STATE_NC, 
    CASE WHEN State = 'ND' THEN 1 ELSE 0 END as STATE_ND, 
    CASE WHEN State = 'OH' THEN 1 ELSE 0 END as STATE_OH, 
    CASE WHEN State = 'OK' THEN 1 ELSE 0 END as STATE_OK, 
    CASE WHEN State = 'OR' THEN 1 ELSE 0 END as STATE_OR, 
    CASE WHEN State = 'PA' THEN 1 ELSE 0 END as STATE_PA, 
    CASE WHEN State = 'RI' THEN 1 ELSE 0 END as STATE_RI, 
    CASE WHEN State = 'SC' THEN 1 ELSE 0 END as STATE_SC, 
    CASE WHEN State = 'SD' THEN 1 ELSE 0 END as STATE_SD, 
    CASE WHEN State = 'TN' THEN 1 ELSE 0 END as STATE_TN, 
    CASE WHEN State = 'TX' THEN 1 ELSE 0 END as STATE_TX, 
    CASE WHEN State = 'UT' THEN 1 ELSE 0 END as STATE_UT, 
    CASE WHEN State = 'VT' THEN 1 ELSE 0 END as STATE_VT, 
    CASE WHEN State = 'VA' THEN 1 ELSE 0 END as STATE_VA, 
    CASE WHEN State = 'WA' THEN 1 ELSE 0 END as STATE_WA, 
    CASE WHEN State = 'WV' THEN 1 ELSE 0 END as STATE_WV, 
    CASE WHEN State = 'WI' THEN 1 ELSE 0 END as STATE_WI, 
    CASE WHEN State = 'WY' THEN 1 ELSE 0 END as STATE_WY
FROM BABYTABLE;

结果:

4. 市场篮子分析

市场篮子分析是购物篮分析和挖掘关联规则的一种方法。在这个过程中,首先需要对数据进行格式化,以便将每笔交易聚合到单个记录中。对于个人电脑来说,这一步骤可能很具挑战性,因为它涉及数据处理和转换。然而,数据仓库专为高效处理这些数据而设计,因此它更适合执行市场篮子分析所需的数据格式化任务。数据仓库提供了便捷的功能,使数据格式化更加容易,从而支持购物篮分析和关联规则的挖掘。

WITH order_detAIl as (
  SELECT 
    SALESORDERNUMBER, 
    listagg(ENGLISHPRODUCTNAME, ', ') WITHIN group (
      order by 
        ENGLISHPRODUCTNAME
    ) as ENGLISHPRODUCTNAME_listagg, 
    COUNT(ENGLISHPRODUCTNAME) as num_products 
  FROM 
    transactions 
  GROUP BY 
    SALESORDERNUMBER
) 
SELECT 
  ENGLISHPRODUCTNAME_listagg, 
  count(SALESORDERNUMBER) as NumTransactions 
FROM 
  order_detail 
where 
  num_products > 1 
GROUP BY 
  ENGLISHPRODUCTNAME_listagg 
order by 
  count(SALESORDERNUMBER) desc;

结果:

5. 时间序列聚合

时间序列聚合是指将时间序列数据按照一定的时间间隔进行汇总和聚合,广泛用语分析数据。然而,要正确执行时间序列聚合,关键因素之一是需要对数据进行适当的格式化,尤其是在使用窗口函数时。这一步骤的正确处理对于获得准确且有意义的聚合结果至关重要。

例如,假设我们想计算过去14天的平均销售额。使用窗口函数的方法要求我们将所有销售数据转换为每天一行的格式。然而,实际上,大多数销售数据是以交易级别存储的,这让格式转换变得困难。这就是时间序列聚合发挥作用的地方。通过时间序列聚合,我们可以创建历史指标的聚合结果,无需重新格式化整个数据集。如果我们想一次添加多个指标,它也会派上用场:

  • 过去14天内的平均销售额
  • 过去6个月中最大的购买
  • 统计过去 90 天内的不同产品类型

如果您想使用窗口函数,则需要通过几个步骤独立构建每个指标。

处理此问题的更好方法是使用公共表表达式 (CTE) 来定义每个预先聚合的历史窗口。

例如:

WITH BASIC_OFFSET_14DAY AS (
  SELECT 
    A.CustomerID, 
    A.TransactionDate, 
    AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST14DAY, 
    MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST14DAY, 
    COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST14DAY
  FROM 
    My_First_Table A 
    INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID 
    AND 1 = 1 
  WHERE 
    B.TransactionDate >= DATEADD(day, -14, A.TransactionDate) 
    AND B.TransactionDate <= A.TransactionDate 
  GROUP BY 
    A.CustomerID, 
    A.TransactionDate
), 
BASIC_OFFSET_90DAY AS (
  SELECT 
    A.CustomerID, 
    A.TransactionDate, 
    AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST90DAY, 
    MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST90DAY, 
    COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST90DAY
  FROM 
    My_First_Table A 
    INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID 
    AND 1 = 1 
  WHERE 
    B.TransactionDate >= DATEADD(day, -90, A.TransactionDate) 
    AND B.TransactionDate <= A.TransactionDate 
  GROUP BY 
    A.CustomerID, 
    A.TransactionDate
), 
BASIC_OFFSET_180DAY AS (
  SELECT 
    A.CustomerID, 
    A.TransactionDate, 
    AVG(B.PurchaseAmount) as AVG_PURCHASEAMOUNT_PAST180DAY, 
    MAX(B.PurchaseAmount) as MAX_PURCHASEAMOUNT_PAST180DAY, 
    COUNT(DISTINCT B.TransactionID) as COUNT_DISTINCT_TRANSACTIONID_PAST180DAY
  FROM 
    My_First_Table A 
    INNER JOIN My_First_Table B ON A.CustomerID = B.CustomerID 
    AND 1 = 1 
  WHERE 
    B.TransactionDate >= DATEADD(day, -180, A.TransactionDate) 
    AND B.TransactionDate <= A.TransactionDate 
  GROUP BY 
    A.CustomerID, 
    A.TransactionDate
) 
SELECT 
  src.*, 
  BASIC_OFFSET_14DAY.AVG_PURCHASEAMOUNT_PAST14DAY, 
  BASIC_OFFSET_14DAY.MAX_PURCHASEAMOUNT_PAST14DAY, 
  BASIC_OFFSET_14DAY.COUNT_DISTINCT_TRANSACTIONID_PAST14DAY, 
  BASIC_OFFSET_90DAY.AVG_PURCHASEAMOUNT_PAST90DAY, 
  BASIC_OFFSET_90DAY.MAX_PURCHASEAMOUNT_PAST90DAY, 
  BASIC_OFFSET_90DAY.COUNT_DISTINCT_TRANSACTIONID_PAST90DAY, 
  BASIC_OFFSET_180DAY.AVG_PURCHASEAMOUNT_PAST180DAY, 
  BASIC_OFFSET_180DAY.MAX_PURCHASEAMOUNT_PAST180DAY, 
  BASIC_OFFSET_180DAY.COUNT_DISTINCT_TRANSACTIONID_PAST180DAY 
FROM 
  My_First_Table src 
  LEFT OUTER JOIN BASIC_OFFSET_14DAY ON BASIC_OFFSET_14DAY.TransactionDate = src.TransactionDate 
  AND BASIC_OFFSET_14DAY.CustomerID = src.CustomerID 
  LEFT OUTER JOIN BASIC_OFFSET_90DAY ON BASIC_OFFSET_90DAY.TransactionDate = src.TransactionDate 
  AND BASIC_OFFSET_90DAY.CustomerID = src.CustomerID 
  LEFT OUTER JOIN BASIC_OFFSET_180DAY ON BASIC_OFFSET_180DAY.TransactionDate = src.TransactionDate 
  AND BASIC_OFFSET_180DAY.CustomerID = src.CustomerID;

结果:

结语

希望本篇文章有助于阐明数据从业者在操作现代数据堆栈时会遇到的不同问题。当涉及查询云数据仓库时,SQL是一把双刃剑。虽然将计算集中在云数据仓库中可以提高速度,但有时需要一些额外的SQL技能。



Tags:SQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: SQL  点击:(5)  评论:(0)  加入收藏
原来 SQL 函数是可以内联的!
介绍在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器...【详细内容】
2024-04-03  Search: SQL  点击:(3)  评论:(0)  加入收藏
如何正确选择NoSQL数据库
译者 | 陈峻审校 | 重楼Allied Market Research最近发布的一份报告指出,业界对于NoSQL数据库的需求正在持续上升。2022年,全球NoSQL市场的销售额已达73亿美元,预计到2032年将达...【详细内容】
2024-03-28  Search: SQL  点击:(13)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: SQL  点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: SQL  点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: SQL  点击:(23)  评论:(0)  加入收藏
在 SQL 中写了 in 和 not in,技术总监说要炒了我……
WHY?IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?1、效率低项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。但是这样一句查询 &darr;select *...【详细内容】
2024-03-18  Search: SQL  点击:(5)  评论:(0)  加入收藏
应对慢SQL的致胜法宝:7大实例剖析+优化原则
大促备战,最大的隐患项之一就是慢SQL,对于服务平稳运行带来的破坏性最大,也是日常工作中经常带来整个应用抖动的最大隐患,在日常开发中如何避免出现慢SQL,出现了慢SQL应该按照什...【详细内容】
2024-03-14  Search: SQL  点击:(4)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: SQL  点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: SQL  点击:(26)  评论:(0)  加入收藏
▌简易百科推荐
向量数据库落地实践
本文基于京东内部向量数据库vearch进行实践。Vearch 是对大规模深度学习向量进行高性能相似搜索的弹性分布式系统。详见: https://github.com/vearch/zh_docs/blob/v3.3.X/do...【详细内容】
2024-04-03  京东云开发者    Tags:向量数据库   点击:(4)  评论:(0)  加入收藏
原来 SQL 函数是可以内联的!
介绍在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器...【详细内容】
2024-04-03  红石PG  微信公众号  Tags:SQL 函数   点击:(3)  评论:(0)  加入收藏
如何正确选择NoSQL数据库
译者 | 陈峻审校 | 重楼Allied Market Research最近发布的一份报告指出,业界对于NoSQL数据库的需求正在持续上升。2022年,全球NoSQL市场的销售额已达73亿美元,预计到2032年将达...【详细内容】
2024-03-28    51CTO  Tags:NoSQL   点击:(13)  评论:(0)  加入收藏
为什么数据库连接池不采用 IO 多路复用?
这是一个非常好的问题。IO多路复用被视为是非常好的性能助力器。但是一般我们在使用DB时,还是经常性采用c3p0,tomcat connection pool等技术来与DB连接,哪怕整个程序已经变成以...【详细内容】
2024-03-27  dbaplus社群    Tags:数据库连接池   点击:(12)  评论:(0)  加入收藏
八个常见的数据可视化错误以及如何避免它们
在当今以数据驱动为主导的世界里,清晰且具有洞察力的数据可视化至关重要。然而,在创建数据可视化时很容易犯错误,这可能导致对数据的错误解读。本文将探讨一些常见的糟糕数据可...【详细内容】
2024-03-26  DeepHub IMBA  微信公众号  Tags:数据可视化   点击:(6)  评论:(0)  加入收藏
到底有没有必要分库分表,如何考量的
关于是否需要进行分库分表,可以根据以下考量因素来决定: 数据量和负载:如果数据量巨大且负载压力较大,单一库单一表可能无法满足性能需求,考虑分库分表。 数据增长:预估数据增长...【详细内容】
2024-03-20  码上遇见你  微信公众号  Tags:分库分表   点击:(13)  评论:(0)  加入收藏
在 SQL 中写了 in 和 not in,技术总监说要炒了我……
WHY?IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?1、效率低项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。但是这样一句查询 &darr;select *...【详细内容】
2024-03-18  dbaplus社群    Tags:SQL   点击:(5)  评论:(0)  加入收藏
应对慢SQL的致胜法宝:7大实例剖析+优化原则
大促备战,最大的隐患项之一就是慢SQL,对于服务平稳运行带来的破坏性最大,也是日常工作中经常带来整个应用抖动的最大隐患,在日常开发中如何避免出现慢SQL,出现了慢SQL应该按照什...【详细内容】
2024-03-14  京东云开发者    Tags:慢SQL   点击:(4)  评论:(0)  加入收藏
过去一年,我看到了数据库领域的十大发展趋势
作者 | 朱洁策划 | 李冬梅过去一年,行业信心跌至冰点2022 年中,红衫的一篇《适应与忍耐》的报告,对公司经营提出了预警,让各个公司保持现金流,重整团队,想办法增加盈利。这篇报告...【详细内容】
2024-03-12    InfoQ  Tags:数据库   点击:(26)  评论:(0)  加入收藏
SQL优化的七个方法,你会哪个?
一、插入数据优化 普通插入:在平时我们执行insert语句的时候,可能都是一条一条数据插入进去的,就像下面这样。INSERT INTO `department` VALUES(1, &#39;研发部(RD)&#39;, &#39...【详细内容】
2024-03-07  程序员恰恰  微信公众号  Tags:SQL优化   点击:(19)  评论:(0)  加入收藏
站内最新
站内热门
站内头条