专栏首页SQL实现SQL 行转列+窗口函数的实例

SQL 行转列+窗口函数的实例

今天继续和大家分享 HackerRank 上的 SQL 编程挑战的解题思路,这一次的题目叫做“Occupations”,属于中等难度级别,答案提交的成功率在 90% 左右。

问题描述

有一个职业表(OCCUPATIONS ),它有两个字段:Name、Occupation。其中,Occupation 的值只有四种:Doctor、Professor、Singer 和 Actor 。

OCCUPATIONS 表的表结构及数据如下:

Column

Type

Name

String

Occupation

String

OCCUPATIONS 的表结构

Name

Occupation

Samantha

Doctor

Julia

Actor

Maria

Actor

Meera

Singer

Ashley

Professor

Ketty

Professor

Christeen

Professor

Jane

Actor

Jenny

Doctor

Priya

Singer

OCCUPATIONS 的表数据

要求:

  1. 将数据按照 Doctor、Professor、Singer 、Actor 的顺序分成四列输出;
  2. 每一列的数据从上到下按姓名的字母顺序升序排序;
  3. 人员不足的职位列上用 NULL 填充。

输出结果:

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

解决方案

一般遇到“将一列中的所有唯一值作为新的列名输出”这种需求,我们首先想到的就应该是行转列,我之前写过一篇介绍SQL 行转列的通用实现的文章,感兴趣的朋友点进来看看。

做行转列时分组的依据是什么呢?即依据哪个字段分组。答案是依据每个职位中姓名的排序序号作为分组条件,而每个职位里面姓名的出现的序号可通过窗口函数求得。

先来看看对职业为 “Actor” 和 “Doctor” 的数据做组内排序。

SELECT
  occupation as p,
  row_number () over (
    PARTITION BY occupation
ORDER BY name
) AS rn,
name
FROM
  OCCUPATIONS
WHERE
  occupation IN ('Actor','Doctor')

排序的结果:

p      rn name
------ -- --------
Actor  1  Eve
Actor  2  Jennifer
Actor  3  Ketty
Actor  4  Samantha
Doctor 1  Aamina
Doctor 2  Julia
Doctor 3  Priya

最后,我们把窗口函数和行转列结合起来,就可以实现题目所要求的输出。

WITH t AS
(SELECT
  occupation,
  row_number () over (
    PARTITION BY occupation
ORDER BY NAME
) AS rn,
NAME
FROM
  OCCUPATIONS)
SELECT
  MAX(
    IF(occupation = 'Doctor', NAME, NULL)
  ) AS Doctor,
  MAX(
    IF(
      occupation = 'Professor',
      NAME,
      NULL
    )
  ) AS Professor,
  MAX(
    IF(occupation = 'Singer', NAME, NULL)
  ) AS Singer,
  MAX(IF(occupation = 'Actor', NAME, NULL)) AS Actor
FROM
  t
GROUP BY rn

如果你的 MySQL 数据库的版本在 8.0 之前,那么可以用用户变量替代窗口函数实现组内排序的功能。

SELECT
  MAX(
    IF(occupation = 'Doctor', NAME, NULL)
  ) AS Doctor,
  MAX(
    IF(
      occupation = 'Professor',
      NAME,
      NULL
    )
  ) AS Professor,
  MAX(
    IF(occupation = 'Singer', NAME, NULL)
  ) AS Singer,
  MAX(IF(occupation = 'Actor', NAME, NULL)) AS Actor
FROM
  (SELECT
    NAME,
    occupation,
    @rn := IF(@oc = occupation, @rn + 1, 1) AS seq,
    @oc := occupation
  FROM
    occupations a,
    (SELECT
      @rn := 0,
      @oc := NULL) b
  ORDER BY occupation,name
  ) t
  GROUP BY seq

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-07-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 删除列中的 NULL 值

    今天接到一个群友的需求,有一张表的数据如图 1,他希望能通过 SQL 查询出图 2 的结果。

    白日梦想家
  • SQL 行转列

    如果你想熟练写各种统计报表的 SQL,那么行转列是你绕不开的一个点,你必须得掌握它。

    白日梦想家
  • 不用 UNION 操作符实现 UNION 的效果

    当我们要合并两个表或者多个表的结果时,可使用 UNION ALL 或者 UNION 操作符, UNION 和 UNION ALL 的区别在于前者会对结果集去重...

    白日梦想家
  • Activiti开发案例之创建用户表视图实现关联查询

    在工作流中,最常用的就是待办任务列表了,如果你仅仅是通过 Activiti 自带的 API 进行查询,大多数情况下查询的数据会不准确。

    小柒2012
  • 目前主要的加密技术有哪些?

    信息安全的重要性我们就不需再继续强调了,无论企业还是个人,都对加密软件的稳定性和安全性提出了更高的要求。可迎面而来更让很多人困惑的是当加密软件遍布市场令人应接不...

    超时代
  • unsupervised learning layers for label-free video analysis

    This paper presents two unsupervised learning layers (UL layers) for label-free ...

    用户1908973
  • Ubuntu18.04完美搭建Tensorflow-gpu

    Persist in sharing and promote mutual progress

    公众号guangcity
  • 3分钟将10M Stack Overflow导入Neo4j

    我想演示如何将Stack Overflow快速导入到Neo4j中。之后,您就可以通过查询图表以获取更多信息,然后可以在该数据集上构建应用程序。如果你愿意,我们有...

    轻吻晴雯
  • 花5分钟时间来了解一下高性能网关Kong会有意外收获

    前几天开源发布了 Kong.Net 项目,收到了大量园友的反馈,开源当天就突破了 100 个star ,可喜可贺,但是从侧面也说明,我们 .NetCore 阵营...

    Edison.Ma
  • 花5分钟时间来了解一下高性能网关Kong会有意外收获

    前几天开源发布了 Kong.Net 项目,收到了大量园友的反馈,开源当天就突破了 100 个star ,可喜可贺,但是从侧面也说明,我们 .NetCore 阵营...

    心莱科技雪雁

扫码关注云+社区

领取腾讯云代金券