Obtener datos para el último día de cada semana.

26 Jul, 2023 Programación 0

Quiero obtener los datos del último día de cada semana (domingo). Estoy utilizando la siguiente consulta que me da la última fila para cada día, ¿cómo puedo modificarla para obtener los datos solo del último día de cada semana?

CON
  PuntoEnElTiempo COMO (
   SELECT
     *
   , "row_number"() OVER (PARTITION BY ResourceId, ControlId, itcfid, "date_format"(Datetime, '%Y'), "date_format"(Datetime, '%j') ORDER BY DateTime DESC) rn
   FROM
     empleado
) 
SELECCIONAR *
DE
  PuntoEnElTiempo 
DONDE (rn = 1)

A continuación se muestra un ejemplo de los datos de la semana pasada (lunes a domingo), solo quiero obtener la última fila del domingo (último día de la semana). Esto debe hacerse para un año determinado.

Entrada
ITCFID  ControlId   ResourceId  DateTime
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/19/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/19/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/18/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/18/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/17/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/17/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/16/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/16/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/15/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/15/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/14/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/14/2021 06:01 AM 
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/13/2021 5:00 PM  
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/13/2021 06:01 AM

Salida
ITCFID  ControlId   ResourceId  DateTime
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/19/2021 5:00 PM

Para un ITCFID específico, puede haber múltiples ID de control y el ID de control puede tener múltiples ID de recurso. Quiero obtener para un itcfid específico: ID de control único -> ID de recurso único -> Obtener la última fila para cada itcfid de esa semana.

davy.ai

July 26, 2023 at 2:05 pm

Para modificar la consulta dada y obtener datos solo del último día de cada semana (domingo):

Primero, necesitamos extraer el número de semana y el año de la columna DateTime utilizando las funciones de fecha. Podemos hacer esto agregando el siguiente código a CTE Pointintime:
WEEK(Datetime) AS week_number, YEAR(Datetime) AS year
Luego, necesitamos modificar la partición en la función row_number para particionar por week_number y year además de las otras columnas. Podemos hacer esto reemplazando la cláusula de partición existente con el siguiente código:

PARTITION BY ResourceId, ControlId, itcfid, year, week_number
Finalmente, necesitamos agregar una condición en la cláusula WHERE para filtrar solo las filas con rn = 1 para el domingo (número de día 1). Podemos hacer esto agregando el siguiente código a la cláusula WHERE:

AND DAYOFWEEK(DateTime) = 1

La consulta modificada se verá de la siguiente manera:

WITH
  Pointintime AS (
   SELECT
     *
   , WEEK(Datetime) AS week_number, YEAR(Datetime) AS year
   , row_number() OVER (PARTITION BY ResourceId, ControlId, itcfid, year, week_number ORDER BY DateTime DESC) rn
   FROM
     employee
  ) 
SELECT *
FROM
  Pointintime 
WHERE (rn = 1) AND DAYOFWEEK(DateTime) = 1

Con esta modificación, la consulta devolverá la última fila para cada domingo del año especificado, para cada combinación única de ITCFID, Control ID y Resource ID.

Answer

davy.ai

July 26, 2023 at 2:05 pm
Para modificar la consulta dada y obtener datos solo del último día de cada semana (domingo):
1. Primero, necesitamos extraer el número de semana y el año de la columna DateTime utilizando las funciones de fecha. Podemos hacer esto agregando el siguiente código a CTE Pointintime:
  WEEK(Datetime) AS week_number, YEAR(Datetime) AS year
2. Luego, necesitamos modificar la partición en la función row_number para particionar por week_number y year además de las otras columnas. Podemos hacer esto reemplazando la cláusula de partición existente con el siguiente código:
  
  PARTITION BY ResourceId, ControlId, itcfid, year, week_number
3. Finalmente, necesitamos agregar una condición en la cláusula WHERE para filtrar solo las filas con rn = 1 para el domingo (número de día 1). Podemos hacer esto agregando el siguiente código a la cláusula WHERE:
  
  AND DAYOFWEEK(DateTime) = 1
La consulta modificada se verá de la siguiente manera:
```
WITH
  Pointintime AS (
   SELECT
     *
   , WEEK(Datetime) AS week_number, YEAR(Datetime) AS year
   , row_number() OVER (PARTITION BY ResourceId, ControlId, itcfid, year, week_number ORDER BY DateTime DESC) rn
   FROM
     employee
  ) 
SELECT *
FROM
  Pointintime 
WHERE (rn = 1) AND DAYOFWEEK(DateTime) = 1
```
Con esta modificación, la consulta devolverá la última fila para cada domingo del año especificado, para cada combinación única de ITCFID, Control ID y Resource ID.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Obtener datos para el último día de cada semana.

Answer

davy.ai