配置单元计数另一个表中匹配行的数目

js5cn81o 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(371)

表1：

| ID | Timestamp
| 1  | 1970
| 2  | 1971

表2：

| Timestamp |
| 1970      |
| 1970      |
| 1970      |
| 1971      |
| 1971      |

如何执行一个查询，以这样的方式连接表，从而得到：

| ID | Timestamp | Count
| 1  | 1970      | 3
| 2  | 1971      | 2

基本上 join on timestamp 只是为了数数？

Hive presto

来源：https://stackoverflow.com/questions/44895815/hive-count-number-of-matching-rows-in-another-table

1条答案

按热度按时间

j0pj023g1#

select  t1.ID
       ,t1.`Timestamp`
       ,coalesce (t2.`Count`,0) as `Count`

from    Table1 t1

        left join  (select      `Timestamp`
                                ,count(*)    as `Count`
                    from        Table2
                    group by    `Timestamp`
                    ) t2

        on          t2.`Timestamp` = t1.`Timestamp`

+----+-----------+-------+
| id | timestamp | count |
+----+-----------+-------+
|  1 |      1970 |     3 |
|  2 |      1971 |     2 |
+----+-----------+-------+

赞(0）回复(0）举报 2021-06-26

我来回答

配置单元计数另一个表中匹配行的数目

1条答案

相关问题

热门标签

最新问答