Hive、Oracle、PgSQL实际生产中遇到的问题

前言：做为一名数据开发人员，难免在工作中遇到各种数据库的各种问题，下面总结如下，希望对遇到此类问题的各位同仁有所帮助，也希望大家多多指正和下方留言实际生产中遇到的关于数据库的各种疑难问题和解决方案，大家互相探讨学习

1 怎么进行行列转换?

将上面上图转换为下图形式

Oracle语法：
with temp as(
select ‘50923’ 总在位人数 ,‘4126’ 不在位人数,‘46797’ 在位人数 from dws_fr_qyjbxx_df
)
select num,sort from
temp
unpivot
(num for sort in (总在位人数,不在位人数,在位人数))t;
具体效果如下：

2 需求：需要两张表，这两张表没有关联，需要这两张表查出来的sql结果

法一：

法二：
select bzrs,tssj,(select xm from dws.dws_zrr_bzxx_df) xm from dws.dws_zrr_jzxx_df limit 3;
搞一个子查询(把B表的B1当做第四列) ,SELECT A1,A2,A3,(SELECT B1 FROM B) B1 FROM A
PS:不建议这样搞，写了个标量子查询，被同事发现会被喷的

法三：
select
t1.a1,t1.a2,t1.a3,t2.b1
from A t1
join B t2
on 1=1
结论：推荐第一种和第三种方法，首选第三种方法

3 就是原数据中6月22日没有这个数据，但是最后页面显示想显示这条数据为2021-06-22 西门入口2_门_1 0

解决思路如下：
SELECT to_char(generate_series(to_date(‘2021-01-01’,‘yyyy-mm-dd’), to_date(‘2022-01-01’,‘yyyy-mm-dd’), ‘1 day’),‘yyyy-mm-dd’) as rq;这句sql可以生成一个时间序列，然后以时间为主表，去关联数据；
以日期为主表，就会所有日期都有；需要把resourname对应的维度表也关联进去，日期先关联resourname，然后再关联数据表就有了，相当于就用到三张表，一张日期维度表，一张resourname维度表，一张数据表；以那两张维度表为主表，笛卡尔积后会罗列出所有维度组合，然后再去关联数据，没有的补0
最终结果截图如下：

4 union前后字段超过64个报错

错误原因分析：
是因为union是做了去重的，而hive的去重应该是用的group by的算法实现的
此场景延伸问题如下：
就是前面的表相当于一个拉链表(比如有数据 01 李三 02 李四)，后面表是正常全量表(比如有数据 01 李三 02 李四 03王五)，刚刚上面那个操作是想得到ID为01和02的这个人的ID，如果我用union all加group by 加count(aaa.id)>1是不等效操作了？跟union 加 group by 加count(aaa.id)>1；因为union更像是union all加group by的联合体，hive限制Grouping sets size不能超过64
解决方案如下：
想得到ID为01和02的这个人的ID：
select id from dwd_xcvtc.dwd_xxjs003_hz t1
where exists(select 1 from ods_xcvtc.ods_jzg_jzgjbxx_df t2 where t1.id=t2.id)
想拿03就用not exists
如果是只有ID相同，其他条件不同，需要再改下哪里，比如姓名变了，但是ID没变，而且确定ID一定不会变，比如01 李三 变为 01 李六这样
解决方案如下：

给这里拼条件就好了，如果要判断所有字段的话建议不要在这拼一大串条件了；比如你要判断后面五十几个字段有没有变化，你可以衍生一个字段，根据非ID那50个字段用MD5算法生成一个加密的字符串，判断那个字符串是否相等，MD5算法只要输入相同那输出必然相同，所以只要那些字段相同，那么通过MD5算法得出的结果必然是相同的

5 关于row_number窗口函数失效的问题

如果有分组，组内排序就是1

此处需要注意的是：先分组再排序是组内排序，比如你按照学号分组，因为一个人只有一条记录，所以排出来全都是1。还是要整理清楚需求，然后理解这个函数用法

6 怎么把一个SQL语句直接当做参数一样传到from之后，直接使用？

具体需求：
1 从aaa表存几个字段，字段名q为指标类型，字段名d为计算逻辑，就是form后面的部分
2 在bbb表进行指标计算，select * from (select d from aaa where q=‘班级’) t1
3 把查到的结果插入到ccc表
4 推送到业务库
解决思路：
把字段名d当参数并可以直接使用做为SQL查询语句那就得存储过程或者Python代码或者Shell代码实现；所有维护在表中的东西都只能当一个字符串查出来，不能作为sql的关键字
具体解决：用Python代码+SQL代码实现