這就是我的數據的樣子，emp_id,skills1234,python|java|sql|R|javascript5639,C|HTML|php|perl這就是數據需要加載到表中的方式emp_id skills python1234 java sql R perl C5639 HTML php其實我已經替換了| 與 \n 但它沒有被加載到下一行，而是只是通過添加空格來加載。我將使用 python etl 將數據加載到表中，因此即使是后處理我也可以添加。有什么建議么？？
1 回答
慕少森
TA貢獻2019條經驗 獲得超9個贊
這是關系數據庫的行為，不能僅emp_id每個組顯示一次并且其所有元素每行顯示一個。改變數據顯示方式是前端的特權和責任，而不是數據庫的。因此，請在 Python 中執行此操作。
話雖如此，Impala 具有SPLIT_PART()字符串函數，它返回由作為參數傳遞的分隔符分隔的字符串的第 n 個標記。
因此，與一系列連續整數交叉連接，然后應用SPLIT_PART(skills,'|',i)即可滿足您的需要。
實際上，以我（從來沒有）謙虛的觀點來看，每當有人向您拋出格式如此不合適的文件以將其加載到數據庫中時，您就應該這樣做。始終使用下面的技術垂直化逗號/條/分號/或任何分隔的“值”列表，并垂直存儲數據。：
WITH
-- your input
input( emp_id,skills) AS (
SELECT 1234,'python|java|sql|R|javascript'
UNION ALL SELECT 5639,'C|HTML|php|perl'
)
,
-- a big enough series of integers ..
i(i) AS (
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
)
SELECT
emp_id
, SPLIT_PART(skills,'|',i) AS skill
FROM input
CROSS JOIN i
WHERE SPLIT_PART(skills,'|',i) <> ''
ORDER BY
emp_id
, i
;
-- out emp_id | skill
-- out --------+------------
-- out 1234 | python
-- out 1234 | java
-- out 1234 | sql
-- out 1234 | R
-- out 1234 | javascript
-- out 5639 | C
-- out 5639 | HTML
-- out 5639 | php
-- out 5639 | perl
使用兩個橫杠/逗號分隔的列，它可能如下所示：
WITH
-- your input, enhanced
input( emp_id,skills,pubs) AS (
SELECT 1234,'python|java|sql|R|javascript','ship inn,anchor,stag'
UNION ALL SELECT 5639,'C|HTML|php|perl' ,'black horse,crown,mitre'
)
,
-- a big enough series of integers ..
i(i) AS (
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
)
,
-- another big enough series of integers ..
j(j) AS (
SELECT
i AS j
FROM i
)
SELECT
emp_id
, i AS skill_sequence
, SPLIT_PART(skills,'|',i) AS skill
, j AS pub_sequence
, SPLIT_PART(pubs,',',j) AS pub
FROM input
CROSS JOIN i
CROSS JOIN j
WHERE SPLIT_PART(skills,'|',i) <> ''
AND SPLIT_PART(pubs, ',',j) <> ''
ORDER BY
emp_id
, i
, j
;
-- out emp_id | skill_sequence | skill | pub_sequence | pub
-- out --------+----------------+------------+--------------+-------------
-- out 1234 | 1 | python | 1 | ship inn
-- out 1234 | 1 | python | 2 | anchor
-- out 1234 | 1 | python | 3 | stag
-- out 1234 | 2 | java | 1 | ship inn
-- out 1234 | 2 | java | 2 | anchor
-- out 1234 | 2 | java | 3 | stag
-- out 1234 | 3 | sql | 1 | ship inn
-- out 1234 | 3 | sql | 2 | anchor
-- out 1234 | 3 | sql | 3 | stag
-- out 1234 | 4 | R | 1 | ship inn
-- out 1234 | 4 | R | 2 | anchor
-- out 1234 | 4 | R | 3 | stag
-- out 1234 | 5 | javascript | 1 | ship inn
-- out 1234 | 5 | javascript | 2 | anchor
-- out 1234 | 5 | javascript | 3 | stag
-- out 5639 | 1 | C | 1 | black horse
-- out 5639 | 1 | C | 2 | crown
-- out 5639 | 1 | C | 3 | mitre
-- out 5639 | 2 | HTML | 1 | black horse
-- out 5639 | 2 | HTML | 2 | crown
-- out 5639 | 2 | HTML | 3 | mitre
-- out 5639 | 3 | php | 1 | black horse
-- out 5639 | 3 | php | 2 | crown
-- out 5639 | 3 | php | 3 | mitre
-- out 5639 | 4 | perl | 1 | black horse
-- out 5639 | 4 | perl | 2 | crown
-- out 5639 | 4 | perl | 3 | mitre
添加回答
舉報
0/150
提交
取消