计算同一表行中表列的更改

bn31dyow 于 2021-07-27 发布在 Java

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

我正在努力想办法做这样的事。我有一个类似这样的sql表。我想添加一个列，其中列出了一年中每个产品拥有的servicer id的总数。在名为“服务年数”的表格中：

+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| Product ID  | Jan Servicer ID | Feb Servicer ID | Mar Servicer ID | Apr Servicer ID | May Servicer ID | Jun Servicer ID | Jul Servicer ID | Aug Servicer ID | Sep Servicer ID | Oct Servicer ID | Nov Servicer ID | Dec Servicer ID | Years Servicer Count |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-001-2015 | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-02      | SRVR-ID-02      | 2                    |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-002-2015 | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | 1                    |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+
| ID-002-2016 | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-01      | SRVR-ID-02      | SRVR-ID-02      | SRVR-ID-02      | SRVR-ID-02      | SRVR-ID-02      | SRVR-ID-02      | SRVR-ID-03      | SRVR-ID-03      | 3                    |
+-------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+----------------------+

我试着在一列中计算不同值的数量。任何朝着正确方向的轻推都是惊人的。

sql count Database unpivot netezza

来源：https://stackoverflow.com/questions/63162090/count-changes-in-table-columns-in-same-table-row

1条答案

按热度按时间

xriantvc1#

一个选项使用 union all 在子查询中，取消将列拆分为行，然后 count(distinct) :

select
    t.*,
    (
        select count(distinct servicer_id)
        from (
            select t.jan_servicer_id as servicer_id
            union all select t.feb_servicer_id
            union all select t.mar_servicer_id
            ...
            union all select t.dec_servicer_id
        ) t
    ) as years_servicer_count
from mytable t

如果netezza的嵌套太多，那么另一种方法是连接：

select t.*, s.years_servicer_count

from mytable t
inner join (
    select product_id, count(distinct servicer_id) as years_servicer_count
    from (
        select product_id, jan_servicer_id as servicer_id from mytable
        union all select product_id, feb_servicer_id from mytable
        union all select product_id, mar_servicer_id from mytable
        ...
        union all select product_id, dec_servicer_id from mytable
    ) t
    group by product_id
) s on s.product_id = t.product_id

赞(0）回复(0）举报 2021-07-27

我来回答

计算同一表行中表列的更改

1条答案

相关问题

热门标签

最新问答