Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-05-13· OpenAIAi2 WildChat-4.8M (AI2)

sql create local temp table autourlsc on commit preserve rows as select case when url…

  1. 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=

  2. Assistant

    Got it! You want to create a local temporary table and replace instances of query= with q= in the URL before executing the rest of your logic. Here's how you can achieve it:

    1. Replace query= with q= in the URL.
    2. 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:

    1. Replace query= with q=:

      • replace(url, 'query=', 'q='): This function call replaces all occurrences of query= with q= in the URL.
    2. Nested CASE Handling:

      • Outer CASE: Checks if the url contains the word app_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: substr returns a substring starting from the position found by instr, ensuring the URL starts with the desired prefix.
    3. Filter (WHERE clause):

      • 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 temp table auto_urls_c with the corrected and filtered URLs, preserving only rows that contain the parameter &q= in the URL.