Converting CTE SQL statement to fit Tableau standards-CodePudding

I am creating a custom query in Tableau, but it doesn't seem to support CTE statements.

with cte (domain, ip_addr, time_col) as
(
select 'Google',101,'2020-03-31 14:55:37'
UNION select 'Google',101,'2020-03-31 14:56:12'
union select 'Facebook',101,'2020-03-31 14:57:36'
union select 'Amazon',101,'2020-03-31 14:57:45'
)

select
domain,
ip_addr,
time_col,
sum(switches) over (partition by ip_addr) -1
from (
select *,
case when lag (domain) over (partition by ip_addr order by time_col) = domain then 0 else 1 end as switches
    
from cte
) t

How can I modify the above query to remove the CTE statement but keep the same results?

CodePudding user response：

Honestly, it seems like you would be better off with a VALUES table construct, rather than an expensive UNION (not even UNION ALL) query:

SELECT DT.domain,
       DT.ip_addr,
       DT.time_col,
       SUM(DT.switches) OVER (PARTITION BY DT.ip_addr) - 1 AS ColumnAliasGoesHere
FROM (SELECT V.domain,
             V.ip_addr,
             V.time_col,
             CASE WHEN LAG(V.domain) OVER (PARTITION BY V.ip_addr ORDER BY V.time_col) = V.domain THEN 0 ELSE 1 END AS switches
      FROM (VALUES ('Google', 101, '2020-03-31 14:55:37'),
                   ('Google', 101, '2020-03-31 14:56:12'),
                   ('Facebook', 101, '2020-03-31 14:57:36'),
                   ('Amazon', 101, '2020-03-31 14:57:45')) V (domain, ip_addr, time_col) ) DT;

CodePudding user response：

select
 domain,
 ip_addr,
 time_col,
 sum(switches) over (partition by ip_addr) -1
from (
      select *,
         case when lag (domain) over (partition by ip_addr order by time_col) = domain then 0 else 1 end as switches

      from 
       (
           select 'Google',101,'2020-03-31 14:55:37'
           UNION select 'Google',101,'2020-03-31 14:56:12'
           union select 'Facebook',101,'2020-03-31 14:57:36'
           union select 'Amazon',101,'2020-03-31 14:57:45'
      )cte(domain, ip_addr, time_col)
 ) t