前端之家

  1. 首页
  2. 问答

在列中找到值

• 问答

我正在将Elastic Load Balancing access日志传递给Athena，我想获取位于URL列中的值。下面的代码适用于mySQL，但是Athena使用SQL可以从下面的方法中获取County的价值吗？

create table elb_logs(row varchar(100),url varchar(100));
insert into elb_logs values("Row1","Lauguage=English&Country=USA&Gender=Male");
insert into elb_logs values("Row2","Gender=Female&Language=French&Country=");
insert into elb_logs values("Row3","Country=Canada&Gender=&Language=English");
insert into elb_logs values("Row4","Gender=&Language=English");

SELECT `row`,IF(LOCATE('Country=',url)>0,COALESCE(
    NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(url,'Country=',-1),'&',1),''),'Blank string is not valid!'),'Missing Country!') AS ColumnB     
FROM `elb_logs`

+------+----------------------------+
| row  | ColumnB                    |
+------+----------------------------+
| Row1 | USA                        |
| Row2 | Blank string is not valid! |
| Row3 | Canada                     |
| Row4 | Missing Country!           |
+------+----------------------------+
ningruohai1 回答：在列中找到值

SPLIT_PART函数在这里似乎可以使用：

SELECT
    row,CASE WHEN POSITION('Country=' IN url) > 0
         THEN SPLIT_PART(SPLIT_PART(url,'Country=',2),'&',1)
         ELSE 'Missing Country!' END AS ColumnB
FROM elb_log;
,

使用Cross Apply 

SELECT row,CASE WHEN LEFT(T2.URL,charindex('&',T2.URL) - 1) = '' THEN 'Blank string is not valid!' 
                 WHEN LEFT(T2.URL,T2.URL) - 1) LIKE '%=%' THEN 'Missing Country!' 
            ELSE LEFT(T2.URL,T2.URL) - 1)   END AS Country
FROM elb_logs AS T
CROSS APPLY (SELECT STUFF(T.URL,1,charindex('&Country',T.URL) + 8,'')+'&') as T2(URL);
amazon-athenaamazon-web-servicesmysqlsql
本文链接：https://www.f2er.com/3167661.html

大家都在问