hive - Vertica-¿Hay funcionalidad LATERAL VIEW?
(1)
De hecho, tengo un escenario práctico que podría coincidir con sus requisitos:
Fuera de esto:
id|day_strt |sales_01 |sales_02 |sales_03 |sales_04 |sales_05 |sales_06
1|2016-01-19 08:00:00| 1,842.25| 5,449.40|- |39,776.86|- | 9,424.10
2|2016-01-19 08:00:00|73,810.66|- | 9,867.70|- |76,723.91|95,605.14
Hacer esto:
id|day_strt |sales_01 |sales_02 |sales_03 |sales_04 |sales_05 |sales_06
1|2016-01-19 08:00:00| 1,842.25| 5,449.40|22,613.13|39,776.86|24,600.48| 9,424.10
2|2016-01-19 08:00:00|73,810.66|41,839.18| 9,867.70|43,295.81|76,723.91|95,605.14
01 a 06 se refiere a la n-ésima hora del día en que se registraron las ventas, a partir de las 08:00.
A continuación se muestra todo el escenario, incluidos los datos de entrada iniciales.
- los datos de entrada como SELECT .. UNION ALL SELECT ....
- Una tabla que consta de 6 enteros para CROSS JOIN a la tabla de 1.
- El pivote vertical: Cruce la entrada con los 6 enteros y, según el índice, solo la n-ésima columna de ventas en una expresión CASE. Finalmente, filtre siempre que la misma expresión CASE evalúe a NULL.
- Complete los espacios usando la cláusula TIMESERIES e interpolación lineal: las cifras de ventas y también la columna de indexación.
- Pivote horizontal todo de nuevo en la consulta final.
Más eficaz que una UNIÓN en todas las columnas de la tabla, te puedo garantizar eso.
Aquí va:
WITH
-- input
input(id,day_strt,sales_01,sales_02,sales_03,sales_04,sales_05,sales_06) AS (
SELECT 1,''2016-01-19 08:00:00''::TIMESTAMP(0), 1842.25, 5449.40 ,NULL::INT,39776.86 ,NULL::INT, 9424.10
UNION ALL SELECT 2,''2016-01-19 08:00:00''::TIMESTAMP(0),73810.66 ,NULL::INT, 9867.70 ,NULL::INT,76723.91 ,95605.14
)
-- debug
-- SELECT * FROM input;
,
-- 6 months to pivot vertically -> 6 integers
six_idxs(idx) AS (
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
)
,
-- pivot input vertically and remove rows with null measures
-- (could probably add the TIMESERIES clause here directly,
-- but less readable and maintainable)
vert_pivot AS (
SELECT
id
, idx
, TIMESTAMPADD(HOUR,idx-1,day_strt)::TIMESTAMP(0) AS sales_ts
, CASE idx
WHEN 1 THEN sales_01
WHEN 2 THEN sales_02
WHEN 3 THEN sales_03
WHEN 4 THEN sales_04
WHEN 5 THEN sales_05
WHEN 6 THEN sales_06
END AS sales
FROM input
CROSS JOIN six_idxs
WHERE (
CASE idx
WHEN 1 THEN sales_01
WHEN 2 THEN sales_02
WHEN 3 THEN sales_03
WHEN 4 THEN sales_04
WHEN 5 THEN sales_05
WHEN 6 THEN sales_06
END
) IS NOT NULL
)
-- debug:
-- SELECT * FROM vert_pivot;
,
-- gap filling and interpolation
gaps_filled AS (
SELECT
id
, TS_FIRST_VALUE(idx,''LINEAR'') AS idx
, tm_sales_ts::TIMESTAMP(0) AS sales_ts
, TS_FIRST_VALUE(sales,''LINEAR'') AS sales
FROM vert_pivot
TIMESERIES tm_sales_ts AS ''1 HOUR'' OVER(
PARTITION BY id ORDER BY sales_ts
)
)
-- debug
-- SELECT * FROM gaps_filled ORDER BY 1,2;
-- pivot horizontally; final query
SELECT
id
, MIN(sales_ts) AS day_strt
, SUM(CASE idx WHEN 1 THEN sales END)::NUMERIC(7,2) AS sales_01
, SUM(CASE idx WHEN 2 THEN sales END)::NUMERIC(7,2) AS sales_02
, SUM(CASE idx WHEN 3 THEN sales END)::NUMERIC(7,2) AS sales_03
, SUM(CASE idx WHEN 4 THEN sales END)::NUMERIC(7,2) AS sales_04
, SUM(CASE idx WHEN 5 THEN sales END)::NUMERIC(7,2) AS sales_05
, SUM(CASE idx WHEN 6 THEN sales END)::NUMERIC(7,2) AS sales_06
FROM gaps_filled
GROUP BY id
ORDER BY id
;
feliz jugando -
Marco el sano
Es necesario rotar una matriz para hacer el relleno de interpolación / espacio de TIMESERIES, y quisiera evitar el enfoque UNION ALL desordenado e ineficiente. ¿Hay algo como la funcionalidad LIFEAL VIEW EXPLODE de Hive disponible en Vertica?
EDITAR: @marcothesane - gracias por su interesante escenario - Me gusta su enfoque para la interpolación. Voy a jugar más con él y ver cómo va. Parece prometedor.
FYI - aquí está la solución que se me ocurrió: mi hipótesis es que estoy tratando de ver el uso de la memoria a lo largo del tiempo por consulta (y el grupo de usuarios / recursos, etc. básicamente tratando de obtener una métrica de costos). Necesito hacer interpolación para poder ver el uso total en cualquier momento. Así que aquí está mi consulta que hace series de tiempo dividiendo por segundo, luego agrega para dar una métrica de "Megabyte_Seconds" por minuto.
with qry_cte as
(
select
session_id
, request_id
, date_trunc(''second'',start_timestamp) as dat_str
, timestampadd(''ss''
, ceiling(request_duration_ms/1000)::int
, date_trunc(''second'',start_timestamp)
) as dat_end
, ceiling(request_duration_ms/1000)::int as secs
, memory_acquired_mb
from query_requests
where request_type = ''QUERY''
and request_duration_ms > 0
and memory_acquired_mb > 0
)
select date_trunc(''minute'',slice_time) as dat_minute
, count(distinct session_id || request_id::varchar) as queries
, sum(memory_acquired_mb) as mb_seconds
from (
select session_id, request_id, slice_time, ts_first_value(memory_acquired_mb) as memory_acquired_mb
from (
select session_id, request_id, dat_str as dat, memory_acquired_mb from qry_cte
union all
select session_id, request_id, dat_end as dat, memory_acquired_mb from qry_cte
) x
timeseries slice_time as ''1 second'' over (partition by session_id, request_id order by dat)
) x
group by 1 order by 1 desc
;