I have a Postgres table like this https://dbfiddle.uk/?rdbms=postgres_11&fiddle=b39a248ac9dde6400795e8a236e58d02 and it works as expected. but when i use that sql query into cubejs seem it work to slow or some time can not load .
cube(`State`, {
rewriteQueries: false,
sql: `WITH cte AS (
SELECT
id,
timestamp,
machineid,
state,
next_state,
start_time,
end_time,
gs,
lag(gs) over (PARTITION BY id ORDER BY gs)
FROM
(
SELECT
DISTINCT timestamp,
id,
machineid,
timestamp as start_time,
state,
lead("timestamp",1,timestamp) OVER (PARTITION BY machineid ORDER BY timestamp) as end_time,
lead("state",1,state) OVER (PARTITION BY machineid ORDER BY timestamp) as next_state
FROM mstate WHERE ${FILTER_PARAMS.Machine.machineid.filter('machineid')}
) a
LEFT JOIN LATERAL
generate_series(start_time::date + 1, end_time::date, interval '1 day') gs
ON TRUE
)
SELECT
id,
timestamp,
machineid,
state,
next_state,
COALESCE(lag, start_time) AS start_time,
gs - interval '1 second' AS next_time
FROM
cte
WHERE gs IS NOT NULL
UNION
SELECT DISTINCT ON (id)
id,
timestamp,
machineid,
state,
next_state,
CASE WHEN start_time::date = end_time::date THEN start_time ELSE end_time::date END,
end_time
FROM
cte
ORDER BY start_time ASC`,
});
can you help me build right schema sql in Cubejs for it works as expected like origin query in link ?
