表(输入数据)
+--------+---------+
| col_id | col_val |
+--------+---------+
| axc | col_x |
| bdf | col_f |
| cde | col_x |
| yhc | col_f |
| idx | col_a |
| dft | col_b |
+--------+---------+
转换逻辑。基本上是想把列a或列b的值组合成一列作为列ab。尝试如下。但是遇到了一个错误。请帮忙
SELECT a.col_id,IF(array_contains(collect_list(a.map_values['col_x']),'1'),'Y','N') AS col_x,
IF(array_contains(collect_list(a.map_values['col_y']),'1'),'Y','N') AS col_y,
IF(array_contains(collect_list(a.map_values['col_a']),'1'),'Y','N') OR IF(array_contains(collect_list(a.map_values['col_b']),'1'),'Y','N') AS col_ab,
IF(array_contains(collect_list(a.map_values['col_f']),'1'),'Y','N') AS col_f
FROM (
SELECT col_id,
col_val,
map(col_val, '1') map_values
FROM transpose) a GROUP BY a.col_id;
输出数据
+--------+-------+-------+-----------+-------+
| col_id | col_x | col_y | col_ab | col_f |
+--------+-------+-------+-----------+-------+
| axc | Y | N | N | N |
| bdf | N | N | N | Y |
| cde | Y | N | N | N |
| dft | N | Y | Y | N |
| idx | N | N | Y | N |
| yhc | N | N | N | Y |
+--------+-------+-------+-----------+-------+
2条答案
按热度按时间o2gm4chl1#
nlejzf6q2#
带有聚合的用例语句: