sql—如何在不将新值写入表的情况下向现有列添加新值？

xcitsw88 于 2021-06-24 发布在 Hive

关注(0)|答案(2)|浏览(220)

我有下表1：

| yyyy_mm_dd | id | feature         | status        |
|------------|----|-----------------|---------------|
| 2019-05-13 | 2  | pricing         | implemented   |
| 2019-05-13 | 2  | pricing         | first_contact |
| 2019-05-13 | 5  | reviews         | implemented   |
| 2019-05-13 | 5  | pricing         | implemented   |
| 2019-05-13 | 6  | reviews         | first_contact |
| 2019-05-13 | 6  | reviews         | implemented   |
| 2019-05-13 | 6  | promotions_geo  | first_contact |
| 2019-05-13 | 6  | prop_management | first_contact |

有两种状态，已实施和第一次接触。我想介绍第三个将是不接触。这将是ID总数减去处于已实施状态和第一个联系人状态的ID之和。
我可以从如下二级表中获得ID的总数：

select
    count(id)
from
    table2

所以我试着把上面的数据合并，这样我就可以得到ID的总数，然后减去：

select
    yyyy_mm_dd,
    feature,
    count(s.id) as implemented_and_first_contact_total,
    null as total_ids
from
    table1 s
where
    s.yyyy_mm_dd = '2020-05-06'
group by
    1,2,4
union all
select
    null as yyyy_mm_dd,
    null as feature,
    null as implemented_and_first_contact_total,
    count(id) as total_ids
from
    table2

现在我不确定如何从total\ id中减去implemented\和first\ contact\ total，以便得到no\ contact的值，并将其作为status列中的值。也许工会不适合在这里使用？
编辑：输出。假设总共有300个身份证。输出如下所示：

| yyyy_mm_dd | feature         | status        | id_count |
|------------|-----------------|---------------|----------|
| 2019-05-13 | pricing         | implemented   | 2        |
| 2019-05-13 | pricing         | first_contact | 1        |
| 2019-05-13 | pricing         | no_contact    | 297      |
| 2019-05-13 | reviews         | implemented   | 2        |
| 2019-05-13 | reviews         | first_contact | 1        |
| 2019-05-13 | reviews         | no_contact    | 297      |
| 2019-05-13 | promotions_geo  | first_contact | 1        |
| 2019-05-13 | promotions_geo  | no_contact    | 299      |
| 2019-05-13 | prop_management | first_contact | 1        |
| 2019-05-13 | prop_management | no_contact    | 299      |

sql Hive hiveql

来源：https://stackoverflow.com/questions/61797478/how-can-i-add-a-new-value-to-an-existing-column-without-writing-the-new-value-to

2条答案

按热度按时间

yqlxgs2m1#

这就是你想要的吗？

select yyyy_mm_dd,
       (count(distinct id) -
        count(distinct case when status in ('implemented', 'first_contact') then id end)
       ) as no_contact
from t
group by yyyy_mm_dd

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

bsxbgnwa2#

更新：已从中删除不相关的子查询 SELECT 并补充道 cross join 试试这个：

select yyyy_mm_dd, feature, status,
count(id) as id_count
from table1 
group by yyyy_mm_dd, feature, status
union all
select yyyy_mm_dd, feature, 'no_contact' as status, 
(cnt - count(id)) as id_count
from table1 cross join (select count(id) as cnt from table2) 
group by yyyy_mm_dd, feature, cnt;

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

我来回答

sql—如何在不将新值写入表的情况下向现有列添加新值？

2条答案

相关问题

热门标签

最新问答