sql - 连接、计数和添加另一个表中的一列值
问题描述
我有 12 个月度表。为简单起见,假设表只有 2 个:
表格1
| IDA | IDB | TYPE1 |
| 123 | X | 90 |
| 123 | X | 30 |
| 123 | Y | 30 |
表2
| IDA | IDB | TYPE1 |
| 123 | X | 90 |
首先,我必须通过连接 IDA 和 IDB 在每个表中创建一个新 ID。我想在 postgresql 中执行此操作(在 IDA 和 IDB 值之间使用 _),但我认为只能在 bash 中执行此操作(没有 _),可能是这样:
awk -F';' -vOFS=';' '{ $(NF+1)=$1$2 ; print}'
预期的输出是:
表格1
| IDA | IDB | TYPE1 | IDAIDB |
| 123 | X | 90 |123_X |
| 123 | X | 30 |123_X |
| 123 | Y | 30 |123_Y |
表2
| IDA | IDB | TYPE1 | IDAIDB |
| 123 | X | 90 | 123_X |
然后我将两个表都导入到我的 postgresql 数据库中(如果我不在 bash 中进行连接)。现在我需要在所有表中创建一个 IDAIDB 和 TYPE1 唯一组合的寄存器,并计算每个组合的记录数,如下所示:
表3
| TYPE1 | IDAIDB | COUNT |
| 90 | 123_X | 2 |
| 30 | 123_X | 1 |
| 30 | 123_Y | 1 |
为此,我运行以下查询:
COPY (SELECT "TYPE1", "IDAIDB", COUNT (*)
FROM (SELECT "TYPE1", "IDAIDB" FROM "TABLE1" UNION ALL
SELECT "TYPE1", "IDAIDB" FROM "TABLE2") unionall
GROUP BY "TYPE1", "IDAIDB")
TO 'C:\2018\TABLE3.csv' DELIMITER ';' CSV HEADER;
同时,我从两个表中提取了所有唯一的 TYPE1 值,并在 TYPE2 字段中分配了另一个代码(我必须逐个执行此操作,这就是我必须手动编辑 TYPE2 字段的原因Excel电子表格):
表4
| TYPE1 | TYPE2 |
| 30 | 3 |
| 90 | 8 |
我在我的数据库中导入了 TABLE 3 和 TABLE4。
然后我必须将最常见的 TYPE2 值分配给 TABLE3。TYPE2 值存储在 TABLE4 中。预期的输出是:
表3
| TYPE1 | IDAIDB | COUNT | TYPE2 |
| 90 | 123_X | 2 | 8 |
| 30 | 123_X | 1 | 8 |
| 30 | 123_Y | 1 | 3 |
最后,我必须使用 IDAIDB 作为关键字段,将更新后的 TABLE3 中的两个新列 COUNT 和 TYPE2 加入到 TABLE1 和 TABLE2 中。预期结果是:
表格1
| IDA | IDB | TYPE1 | IDAIDB | COUNT | TYPE2 |
| 123 | X | 90 | 123_X | 2 | 8 |
| 123 | X | 30 | 123_X | 1 | 8 |
| 123 | Y | 30 | 123_Y | 1 | 3 |
表2
| IDA | IDB | TYPE1 | IDAIDB | COUNT | TYPE2 |
| 123 | X | 90 | 123_X |1 | 8 |
在上面的示例中,IDAIDB=123_X 在表 1 和表 2 中都采用 TYPE2=8,因为整个数据库中 IDAIDB 和 TYPE1 最常见的组合是 123_X 90(2 条记录)而不是 123_X 30(1 条记录)
我怎样才能做到这一点?我正在尝试完成这项工作,但是我只能先在 bash 中然后在 postgresql 中获得部分结果,我根本无法完成工作的最后一部分,即为每条记录分配 TYPE2根据 IDAIDB 和 TYPE1 的最常见组合,从 TABLE2 中获取值。如果可能,我只想在 bash 或 postgresql 中执行此操作,但这不是强制性的。先感谢您
解决方案
最后一栏有点棘手。我认为这可以满足您的要求:
select t.ida, t.idb, t.type1,
(t.ida || '_' || t.idb) as ida_idb,
count(*) over (partition by t.ida, t.idb, t.type1) as cnt
from (select distinct on (t1.ida, t1.idb, t1.type) t1.*,
t2.type2
from table1 t1 join
table2 t2
on t1.type1 = t2.type1
order by t1.ida, t1.idb, t1.type, count(*) over (partition by t1.ida, t1.idb, t2.type2) desc
) t
推荐阅读
- python-3.x - 用另一个变量循环一个变量范围
- php - 使用 .htaccess 捕获域名
- r - 如何使用for循环将注释添加到ggplot的顶部
- javascript - axios 在 postman 上运行时返回 404 错误
- c - C 使用 ncurses 库使用 getch() 屏蔽密码
- google-apps-script - 记录由谷歌表格制作的任何副本
- batch-file - 如何根据使用 WinSCP 上传的结果将文件批量移动到不同的文件夹
- reactjs - GatsbyJS + Netlify + MDX 加载器问题
- python - Pyexcel,如何使用 pyexcel 将 dict 保存到 csv 文件?
- php - PHP MySQL新手,实时服务器上的身份验证问题