Red de conocimiento informático - Material del sitio web - Generar series de fechas en PostgreSQL y Hive

Generar series de fechas en PostgreSQL y Hive

## 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)".