【SQL开发实战技巧】系列（三十七）：数仓报表场景☞从表内始终只有近两年的数据，要求用两列分别显示其中一年的数据聊行转列隐含信息的重要性

赵延东的一亩三分地 2023-06-11 原文

系列文章目录

文章目录

前言

本篇文章讲解的主要内容是：需求一：T表内始终只有近两年的数据，要求时刻返回两列并且分别显示其中一年的数据。需求二：以Emp表中为例，要求每个job显示为一行，同类job的姓名分列显示，不够的显示为空
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、用"行转列"来得到隐含信息

示例数据如下：

with t as (
select 'A' as shop,'2013' as nyear, 123 as amount from dual 
union all
select 'A' as shop,'2012' as nyear, 200 as amount from dual 
)
select * from t;
SHOP NYEAR     AMOUNT
---- ----- ----------
A    2013         123
A    2012         200

T表内始终只有两年的数据，要求返回两列分别显示其中一年的数据，原始写法如下：

with t as (
select 'A' as shop,'2013' as nyear, 123 as amount from dual 
union all
select 'A' as shop,'2012' as nyear, 200 as amount from dual 
)
select 
max(decode(nyear,'2012',amount)) as s2012,
max(decode(nyear,'2013',amount)) as s2013
from t;
     S2012      S2013
---------- ----------
       200        123

现在的语句中，2012年与2013年是固定的，而数据库中每一年的数据都在变（上一年与本年），现要求不再固定为2012年与2013年。怎么办？
其实这就是把上一年与本年数据各写为两列，本例的max用错了，应该为sum。
首先可以用分析函数取出上一年的年份(min)和本年的年份(max),分别放在两列
里。

with t as
 (select 'A' as shop, '2013' as nyear, 123 as amount
    from dual
  union all
  select 'A' as shop, '2012' as nyear, 200 as amount
    from dual)
select shop,
       nyear,
       max(nyear) over() as max_year,
       min(nyear) over() as min_year,
       sum(amount) as amount
  from t
 group by shop, nyear
SHOP NYEAR MAX_YEAR MIN_YEAR     AMOUNT
---- ----- -------- -------- ----------
A    2012  2013     2012            200
A    2013  2013     2012            123

SQL>

可以看到，我们把年份信息取出来，分别放在了两列中，这样就有了一个参照：

with t as
 (select 'A' as shop, '2013' as nyear, 123 as amount
    from dual
  union all
  select 'A' as shop, '2012' as nyear, 200 as amount
    from dual)
select shop,
       max(decode(nyear, max_year, amount)) as 今年,
       max(decode(nyear, min_year, amount)) as 去年
  from (select shop,
               nyear,
               max(nyear) over() as max_year,
               min(nyear) over() as min_year,
               sum(amount) as amount
          from t
         group by shop, nyear
        )
 group by shop;
 
SHOP         今年         去年
---- ---------- ----------
A           123        200

二、用隐藏数据进行行转列

有网友提出如下需求，用现有表scott.emp模拟，表中每个job显示为一行，同类job的姓名分列显示，不够的显示为空，结果如下：

JOB       S1         S2         S3         S4
--------- ---------- ---------- ---------- ----------
ANALYST   FORD       SCOTT                 
CLERK     ADAMS      JAMES      MILLER     SMITH
MANAGER   BLAKE      CLARK      JONES      
PRESIDENT KING                             
SALESMAN  ALLEN      MARTIN     TURNER     WARD

乍一看，这个语句不好写，因为没有可供case when选用的条件。其实这里的条件是隐藏的，就是各员工姓名的顺序。

with t as (
select ename, job, row_number() over(partition by job order by ename) as rn
  from emp
 order by job)
 select * from t;
ENAME      JOB               RN
---------- --------- ----------
FORD       ANALYST            1
SCOTT      ANALYST            2
ADAMS      CLERK              1
JAMES      CLERK              2
MILLER     CLERK              3
SMITH      CLERK              4
BLAKE      MANAGER            1
CLARK      MANAGER            2
JONES      MANAGER            3
KING       PRESIDENT          1
ALLEN      SALESMAN           1
MARTIN     SALESMAN           2
TURNER     SALESMAN           3
WARD       SALESMAN           4

14 rows selected

有这个序号后，我们可以把sn=l的放第一列，sn=2的放第二列…

with t as (
select ename, job, row_number() over(partition by job order by ename) as rn
  from emp
 order by job)
 select job, 
 max(case when rn=1 then ename end) as s1,
 max(case when rn=2 then ename end) as s2,
 max(case when rn=3 then ename end) as s3,
 max(case when rn=4 then ename end) as s4
 from t
 group by job;
 JOB       S1         S2         S3         S4
--------- ---------- ---------- ---------- ----------
ANALYST   FORD       SCOTT                 
CLERK     ADAMS      JAMES      MILLER     SMITH
MANAGER   BLAKE      CLARK      JONES      
PRESIDENT KING                             
SALESMAN  ALLEN      MARTIN     TURNER     WARD

如果用行转列函数，则为

 select job, s1, s2, s3, s4
   from (select ename,
                job,
                row_number() over(partition by job order by ename) as rn
           from emp
          order by job)
 pivot(max(ename)
    for rn in(1 as s1, 2 as s2, 3 as s3, 4 as s4));
JOB       S1         S2         S3         S4
--------- ---------- ---------- ---------- ----------
ANALYST   FORD       SCOTT                 
CLERK     ADAMS      JAMES      MILLER     SMITH
MANAGER   BLAKE      CLARK      JONES      
PRESIDENT KING                             
SALESMAN  ALLEN      MARTIN     TURNER     WARD

如果不知道有多少列怎么办？那就需要先查询，然后用循环语句自动拼装成需要的SQL然后查询即可：

declare
  v_max number;
  v_sql varchar2(4000);
begin
  select max(count(*)) into v_max from emp group by job;
  v_sql := 'select job, ' || chr(10);
  for i in 1 .. v_max loop
    if (i <> v_max) then
      v_sql := v_sql || ' max(case when rn=' || i ||
               ' then ename end) as s' || i || ',' || chr(10);
    else
      v_sql := v_sql || ' max(case when rn=' || i ||
               ' then ename end) as s' || i || chr(10);
    end if;
  end loop;
  v_sql := v_sql || ' from (
select ename, job, row_number() over(partition by job order by ename) as rn
  from emp
 order by job)  group by job';
  dbms_output.put_line(v_sql);
end;
 
 
select job, 
 max(case when rn=1 then ename end) as s1,
 max(case when rn=2 then ename end) as s2,
 max(case when rn=3 then ename end) as s3,
 max(case when rn=4 then ename end) as s4
 from (
select ename, job, row_number() over(partition by job order by ename) as rn
  from emp
 order by job)  group by job

总结

本篇文章讲解的主要内容是：需求一：T表内始终只有近两年的数据，要求时刻返回两列并且分别显示其中一年的数据。需求二：以Emp表中为例，要求每个job显示为一行，同类job的姓名分列显示，不够的显示为空

隐含有近 span class token sql 数据库行转列返回近两年数据分列显示不同job员工

有关【SQL开发实战技巧】系列（三十七）：数仓报表场景☞从表内始终只有近两年的数据，要求用两列分别显示其中一年的数据聊行转列隐含信息的重要性的更多相关文章

python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2
我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i
ruby-on-rails - 使用一系列等级计算字母等级 - 2
这里是Ruby新手。完成一些练习后碰壁了。练习:计算一系列成绩的字母等级创建一个方法get_grade来接受测试分数数组。数组中的每个分数应介于0和100之间，其中100是最大分数。计算平均分并将字母等级作为字符串返回，即“A”、“B”、“C”、“D”、“E”或“F”。我一直返回错误:avg.rb:1:syntaxerror,unexpectedtLBRACK,expecting')'defget_grade([100,90,80])^avg.rb:1:syntaxerror,unexpected')',expecting$end这是我目前所拥有的。我想坚持使用下面的方法或.join，
ruby - Ruby 有 `Pair` 数据类型吗？ - 2
有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？最佳
ruby - 我如何添加二进制数据来遏制 POST - 2
我正在尝试使用Curbgem执行以下POST以解析云curl-XPOST\-H"X-Parse-Application-Id:PARSE_APP_ID"\-H"X-Parse-REST-API-Key:PARSE_API_KEY"\-H"Content-Type:image/jpeg"\--data-binary'@myPicture.jpg'\https://api.parse.com/1/files/pic.jpg用这个:curl=Curl::Easy.new("https://api.parse.com/1/files/lion.jpg")curl.multipart_form_
世界前沿3D开发引擎HOOPS全面讲解——集3D数据读取、3D图形渲染、3D数据发布于一体的全新3D应用开发工具 - 2
无论您是想搭建桌面端、WEB端或者移动端APP应用，HOOPSPlatform组件都可以为您提供弹性的3D集成架构，同时，由工业领域3D技术专家组成的HOOPS技术团队也能为您提供技术支持服务。如果您的客户期望有一种在多个平台（桌面/WEB/APP，而且某些客户端是“瘦”客户端）快速、方便地将数据接入到3D应用系统的解决方案，并且当访问数据时，在各个平台上的性能和用户体验保持一致，HOOPSPlatform将帮助您完成。利用HOOPSPlatform，您可以开发在任何环境下的3D基础应用架构。HOOPSPlatform可以帮您打造3D创新型产品，HOOPSSDK包含的技术有：快速且准确的CAD
【鸿蒙应用开发系列】- 获取系统设备信息以及版本API兼容调用方式 - 2
在应用开发中，有时候我们需要获取系统的设备信息，用于数据上报和行为分析。那在鸿蒙系统中，我们应该怎么去获取设备的系统信息呢，比如说获取手机的系统版本号、手机的制造商、手机型号等数据。1、获取方式这里分为两种情况，一种是设备信息的获取，一种是系统信息的获取。1.1、获取设备信息获取设备信息，鸿蒙的SDK包为我们提供了DeviceInfo类，通过该类的一些静态方法，可以获取设备信息，DeviceInfo类的包路径为：ohos.system.DeviceInfo.具体的方法如下：ModifierandTypeMethodDescriptionstatic StringgetAbiList()Obt
FOHEART H1数据手套驱动Optitrack光学动捕双手运动(Unity3D) - 2
本教程将在Unity3D中混合Optitrack与数据手套的数据流，在人体运动的基础上，添加双手手指部分的运动。双手手背的角度仍由Optitrack提供，数据手套提供双手手指的角度。 01 客户端软件分别安装MotiveBody与MotionVenus并校准人体与数据手套。MotiveBodyMotionVenus数据手套使用、校准流程参照：https://gitee.com/foheart_1/foheart-h1-data-summary.git02 数据转发打开MotiveBody软件的Streaming，开始向Unity3D广播数据；MotionVenus中设置->选项选择Unit
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
「Python｜Selenium｜场景案例」如何定位iframe中的元素？ - 2
本文主要介绍在使用Selenium进行自动化测试或者任务时，对于使用了iframe的页面，如何定位iframe中的元素文章目录场景描述解决方案具体代码场景描述当我们在使用Selenium进行自动化测试的时候，可能会遇到一些界面或者窗体是使用HTML的iframe标签进行承载的。对于iframe中的标签，如果直接查找是无法找到的，会抛出没有找到元素的异常。比如近在咫尺的例子就是，CSDN的登录窗体就是使用的iframe，大家可以尝试通过F12开发者模式查看到的tag_name,class_name,id或者xpath来定位中的页面元素，会抛出NoSuchElementException异常。解决