当 unpivot 未检测到具有不同类型的字段时,我们如何投射选择查询的所有字段? [英] Snowflake how can we cast all fields of select query when unpivot is not detecting fields with a different types?
问题描述
跟进此问题,我做了以下脚本:
Following up on this question, I made the following script:
select
OBSERVATION_DATE,
name_col,
md5(
md5(
concat_ws(
'',
val_col,
DATE(OBSERVATION_DATE, 'DD/MM/YYYY'),
'CAMP',
CAMPNO
)
)
),
current_timestamp(),
NULL,
'ONA',
val_col,
md5(
concat_ws(
'',
'name_col',
DATE(OBSERVATION_DATE, 'DD/MM/YYYY'),
'CAMP',
CAMPNO
)
)
from
(
select
T.*
FROM
TEMP_TABLE_NAME T
) unpivot (val_col for name_col in (FACILITY_ID, CAMPNO));
它将帮助我转置一个临时表,将所有相关字段添加到另一个表中,其中每个 field/value
对代表一行.
It will help me transpose a temporary table to add all related fields into another table, where each field/value
pair represent a row.
我从这个查询中得到的错误是:
The error I am getting out of this query is:
SQL 编译错误:位置 77 处的错误行 2 无效标识符'坎普诺'
SQL compilation error: error line 2 at position 77 invalid identifier 'CAMPNO'
我想可能是因为campNo是一个数字,所有字段都应该被强制转换为varchar
,所以我尝试了这个查询:
I think it is maybe because the campNo is a number, and all fields should be casted to varchar
, so I tried this query:
select cast(campno as varchar) as CAMPNO FROM TEMP_TABLE_NAME;
select cast(campno as varchar) as CAMPNO FROM TEMP_TABLE_NAME;
它奏效了,但是在第二个 select
语句中我得到了一个错误:
And it worked, but within the second select
statement I got an error:
...from (select cast(CAMPNO as varchar) as campno, T.* FROM TEMP_TABLE_NAME T)
并得到一个错误:
SQL 编译错误:不明确的列名 'CAMPNO'
SQL compilation error: ambiguous column name 'CAMPNO'
我需要将 select * from myTable
中的所有字段转换为 varchar.
I need to cast all fields inside select * from myTable
into varchar.
推荐答案
您收到此错误的原因是,一旦您取消透视,就没有名为 campno
的列.您将该列反透视为行:
The reason you're getting this error is that, once you unpivot, there is no column named campno
. You unpivoted that column into rows:
设置示例
create or replace transient table source_table
(
id number,
observation_date varchar,
facility_id varchar,
campno number
);
insert overwrite into source_table
values (1, '01/01/2020', 2, 23),
(2, '02/01/2020', 3, 44),
(3, '03/01/2020', 1, 123),
(4, '04/01/2020', 1, 2233)
;
如果您从该源表中执行select *
,您将有一列名为campno
和facility_id
:
If you do a select *
from this source table, you have a column called campno
and facility_id
:
select st.* from source_table st;
-- Resulting table:
-- +--+----------------+-----------+------+
-- |ID|OBSERVATION_DATE|FACILITY_ID|CAMPNO|
-- +--+----------------+-----------+------+
-- |1 |01/01/2020 |2 |23 |
-- |2 |02/01/2020 |3 |44 |
-- |3 |03/01/2020 |1 |123 |
-- |4 |04/01/2020 |1 |2233 |
-- +--+----------------+-----------+------+
但是一旦您在 campno
和 facility_id
字段上unpivot
表,那么列名变成了行并且您不能再选择campno,facility_id
.您还需要确保要取消透视的列的数据类型相同(因此是子查询):
But once you unpivot
the table on the campno
and facility_id
fields, then the column names become rows and you can no longer select campno, facility_id
. You also need to make sure the datatypes of the column you're unpivoting on are the same (hence the subquery):
select *
from (
select
id,
observation_date,
facility_id,
campno::varchar as campno
from source_table) unpivot ( val_col for name_col in (facility_id, campno) );
-- Resulting table:
-- +--+----------------+-----------+-------+
-- |ID|OBSERVATION_DATE|NAME_COL |VAL_COL|
-- +--+----------------+-----------+-------+
-- |1 |01/01/2020 |FACILITY_ID|2 |
-- |1 |01/01/2020 |CAMPNO |23 |
-- |2 |02/01/2020 |FACILITY_ID|3 |
-- |2 |02/01/2020 |CAMPNO |44 |
-- |3 |03/01/2020 |FACILITY_ID|1 |
-- |3 |03/01/2020 |CAMPNO |123 |
-- |4 |04/01/2020 |FACILITY_ID|1 |
-- |4 |04/01/2020 |CAMPNO |2233 |
-- +--+----------------+-----------+-------+
看看上面怎么没有名为 campno
或 facility_id
的列?它们已被取消透视为行,不再是列...
See how there is no column named campno
or facility_id
above? They have been unpivoted into rows and are no longer columns...
我认为(但不确定)您可能正在寻找的是:
I think (but not sure) what you might be looking for is:
select
id,
observation_date,
name_col,
val_col,
'ONA',
md5(concat_ws('', id, name_col, val_col, observation_date)),
current_timestamp()
from (
select
id,
to_date(observation_date, 'DD/MM/YYYY') as observation_date,
facility_id::varchar as facility_id,
campno::varchar as campno
from source_table
) unpivot ( val_col for name_col in (facility_id, campno) )
-- Resulting table:
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+
-- |ID|OBSERVATION_DATE|NAME_COL |VAL_COL|'ONA'|MD5(MD5(CONCAT_WS('', ID, NAME_COL, VAL_COL, OBSERVATION_DATE)))|CURRENT_TIMESTAMP() |
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+
-- |1 |2020-01-01 |FACILITY_ID|2 |ONA |19baf986df81f1818afae848cd14fc87 |2021-03-01 09:59:45.919000000 -08:00|
-- |1 |2020-01-01 |CAMPNO |23 |ONA |1fcb518697772362a0dabcba7aacfa8a |2021-03-01 09:59:45.919000000 -08:00|
-- |2 |2020-01-02 |FACILITY_ID|3 |ONA |60a82dbc3d1b78d09519fc50b26026cd |2021-03-01 09:59:45.919000000 -08:00|
-- |2 |2020-01-02 |CAMPNO |44 |ONA |cb03dc5d1df4e2548f26284c5ff339c2 |2021-03-01 09:59:45.919000000 -08:00|
-- |3 |2020-01-03 |FACILITY_ID|1 |ONA |fe0dd77e601f6f3bac4cde8da537eb3d |2021-03-01 09:59:45.919000000 -08:00|
-- |3 |2020-01-03 |CAMPNO |123 |ONA |95604e260fe1a69bc54100b08fee6d87 |2021-03-01 09:59:45.919000000 -08:00|
-- |4 |2020-01-04 |FACILITY_ID|1 |ONA |a94029663591b1c942e9f3be1467e04f |2021-03-01 09:59:45.919000000 -08:00|
-- |4 |2020-01-04 |CAMPNO |2233 |ONA |1d90ae0854a9042bf44906511e90ced8 |2021-03-01 09:59:45.919000000 -08:00|
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+
md5(md5()) 毫无意义,我想我在你的另一篇文章中看到过,所以不确定你为什么要这样做.
It's pointless to md5(md5()) which I think I've seen in another one of your posts so not sure why you're doing that.
这篇关于当 unpivot 未检测到具有不同类型的字段时,我们如何投射选择查询的所有字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!