sql create local temp table autourlsc on commit preserve rows as select case when url…
- User
sql
create local temp table auto_urls_c on commit preserve rows as select case when url ilike '%app_link%' then case when url ilike '%https://www.avito.ru%' then SUBSTRING (url ,INSTR(url,'https://www.avito.ru')) when url ilike '%http://www.avito.ru%' then SUBSTRING (url ,INSTR(url,'http://www.avito.ru')) when url ilike '%https://m.avito.ru%' then SUBSTRING (url ,INSTR(url,'https://m.avito.ru')) else url end else url end as url, event_date, traffic_source, visits from auto_urls1 where url ilike '%&q=%';
when create table -- also make replace query= to q=
- Assistant
Got it! You want to create a local temporary table and replace instances of
query=withq=in the URL before executing the rest of your logic. Here's how you can achieve it:- Replace
query=withq=in the URL. - Create the temporary table with the adjusted URLs and the rest of the dataset.
Here's the SQL statement to accomplish this:
create local temp table auto_urls_c on commit preserve rows as select case when url ilike '%app_link%' then case when url ilike '%https://www.avito.ru%' then substr(replace(url, 'query=', 'q='), instr(replace(url, 'query=', 'q='), 'https://www.avito.ru')) when url ilike '%http://www.avito.ru%' then substr(replace(url, 'query=', 'q='), instr(replace(url, 'query=', 'q='), 'http://www.avito.ru')) when url ilike '%https://m.avito.ru%' then substr(replace(url, 'query=', 'q='), instr(replace(url, 'query=', 'q='), 'https://m.avito.ru')) else replace(url, 'query=', 'q=') end else replace(url, 'query=', 'q=') end as url, event_date, traffic_source, visits from auto_urls1 where url ilike '%&q=%';Explanation:
-
Replace
query=withq=:replace(url, 'query=', 'q='): This function call replaces all occurrences ofquery=withq=in the URL.
-
Nested CASE Handling:
- Outer
CASE: Checks if theurlcontains the wordapp_link. - Inner
CASE: Checks specific patterns to extract the relevant part of the URL starting from predefined substrings (https://www.avito.ru,http://www.avito.ru,https://m.avito.ru). - SUBSTR and INSTR Functions:
substrreturns a substring starting from the position found byinstr, ensuring the URL starts with the desired prefix.
- Outer
-
Filter (
WHEREclause):url ilike '%&q=%': Ensures only entries where the URL contains&q=(case-insensitive) are included in the temporary table.
This script will create a
local temptableauto_urls_cwith the corrected and filtered URLs, preserving only rows that contain the parameter&q=in the URL. - Replace