Generar series de fechas en PostgreSQL y Hive
### Postgresql implementa series de fechas
En postgresql, existe la función generate_series(start_date, end_date, intervalo) para generar series de fechas
``
seleccione fecha(día) como día
de generate_series('2020-05-22'::timestamp, current_date, ' 1 día'::intervalo) como día
```
### La implementación de Hive especifica una secuencia de fechas y especifica la fecha de inicio y finalización
En Hive, esto se puede lograr con posexplode(list), dateiff(end_date, start_date).
Primero cree una tabla llamada calendario, con el campo día y tipo fecha, y almacene un valor de fecha como fecha de inicio, como 2014-01-01. calender VALUES(to_date('2014-01-01T00:00'));
``
Con la ayuda de `datediff(end_date, start_date)`, `space(int_ count)`, `split(list,seperator)`, `posexlode(list) ` genera n espacios y luego se divide en una lista. posexplode convierte las filas en varias columnas y devuelve el índice y el valor.
``
seleccione date_ add(day,idx) como new_day de default.calender
Vista horizontal poseexplode( split( space( dateiff( current_date, to_date ('2014-01-01T00:00:. 00') ) ), ' ')?), ' ')?) tt como idx, v;
```
Explicación del proceso intermedio:
Por ejemplo:
``
select dateiff('2020-06-30','2020-05-1') ; -- 60
select split( space(datediff('2020-06-30','2020-05-1')),' ') -- Genera 60 espacios y luego divide en listas
``
índice | valor
--- ---
0 | 1 | ' '
2 | ' '
...
2 | ''
...
59 | ' '
#### Hive agrega el día de la semana a la tabla de secuencia de fechas
Agrega una nueva columna para guardar el día de la semana
``
ALTER TABLE default.calender ADD COLUMNS(weekday STRING);
``
Con la ayuda de la función `datediff`, `pmod` se puede implementar
dateiff es una función que resta dos fechas
Sintaxis: `datediff(string enddate, string startdate)`
Valor de retorno: int
Descripción: Devuelve la diferencia en días entre dos parámetros de tiempo.
pmod es una función de resto positivo
Sintaxis: pmod(int a, int b), pmod(double a, double b)`
Valor de retorno: int double
Descripción: Devuelve el resto de a positivo dividido por b
Seleccione una fecha de domingo como fecha de referencia.
Elegí 2013-12-29
`pmod(datediff(date, '2012-01-01'), 7)?`
Valor de retorno: int 0-6< / p>
0-6 significa domingo y sábado respectivamente
``
INSERTAR TABLA DE SOBRESCRITURA default.calender
seleccione date_add(day,idx) como `date`,
-- 0-6 representa domingo y sábado respectivamente
case pmod(datediff(date_add(day,idx), to_date('2013-12-29T00: 00 :
Cuando 0 es "Domingo"
Cuando 1 es "Lunes"
Cuando 2 es "Martes"
Cuando 3 o 'el reloj es "miércoles"
Cuando las 4 en punto son "jueves"
Cuando las 5 en punto son "viernes"
Cuando son las 6 en punto es "Sábado" "
FIN es el día laborable
de default.calender
Vista horizontal poseexplode( split( space( dateiff( to_date('2030- 01-01T00:00 :00') , to_date('2014 -01-01-01T00:00:00') ).), ' ')?) tt como idx, v;
``
### Suplemento: implementación de Hive para completar la fecha faltante
Al contar algunos indicadores DIARIOS, generalmente se usa agrupar por. A menudo no hay datos para algunas fechas, lo que da como resultado el resultado final. tabla de resultados Las fechas no son en realidad una secuencia completa continua
Por ejemplo:
store_id | -- --
1 | 2020-04-02 | 2020-04-03 | | 10
2 | 2020-04-03 | 2020-04-06 |
p>1 | 2020-04-08 | 50
...| ...| Los pasos de la solución son los siguientes:
1. Agregue por store_id para encontrar las fechas mínima y máxima.
2. En este momento, según la tabla CTE en el paso 1, de acuerdo con cada store_ID Realizar la finalización de la secuencia de fechas en las fechas mínima y máxima
``
seleccione t.store_id, date_add(t.min_date, idx) como `fecha`
desde store_with_min_max_usage_date t
Vista horizontal posexplode(split(space (datediff(t.max_date, t.min_date)),' ')) pe como idx, v
``< / p>
3. Realice "LLEFT JOIN" entre los resultados del paso 2 y la tabla de resultados de agregación anterior. Para "NULL", reemplace los valores faltantes con "CALESCE(v, 0)".