es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Cómo utilizar row_number para contar fechas después de una fecha de anclaje deseada?

02 Aug, 2023 Programación

Tengo un conjunto de datos donde quiero contar las filas antes y después de mi fecha de referencia. Creo que una función de ventana usando row_number() funcionaría, pero no estoy seguro de cómo se escribiría.

Mi tabla actual:

order_id contact_id placed_at anchor_date
13236647 123456 2020-06-24T12:47:18
16253983 123456 2020-07-19T05:54:52
16720335 123456 2020-08-20T02:02:06
17823059 123456 2020-09-17T02:02:04 2020-09-17T02:02:04
18523920 123456 2020-10-12T13:53:19
19324467 123456 2020-11-12T01:02:18
20234536 123456 2020-12-04T01:02:42
70523487 654321 2015-09-21T09:25:25
71234048 654321 2015-10-01T19:02:28
14145443 654321 2020-03-28T10:21:57
14134525 654321 2020-03-28T10:31:33
11244748 654321 2020-04-03T06:20:57 2020-04-03T06:20:57

Mi resultado deseado se vería así:

rows_before_anchor enumera todas las filas antes de anchor_date ordenadas por placed_at y agrupadas por contact_id.

rows_after_anchor enumera todas las filas después de anchor_date ordenadas por placed_at y agrupadas por contact_id.

Esto es lo que intenté:

SELECT
  order_id,
  contact_id,
  placed_at,
  ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at < anchor_date) AS rows_before_anchor,
  ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at > anchor_date) AS rows_after_anchor
FROM mytable

Mi tabla deseada:

order_id contact_id placed_at anchor_date rows_before_anchor rows_after_anchor
13236647 123456 2020-06-24T12:47:18 1
16253983 123456 2020-07-19T05:54:52 2
16720335 123456 2020-08-20T02:02:06 3
17823059 123456 2020-09-17T02:02:04 2020-09-17T02:02:04
18523920 123456 2020-10-12T13:53:19 1
19324467 123456 2020-11-12T01:02:18 2
20234536 123456 2020-12-04T01:02:42 3
70523487 654321 2015-09-21T09:25:25 1
71234048 654321 2015-10-01T19:02:28 2
14145443 654321 2020-03-28T10:21:57 3
14134525 654321 2020-03-28T10:31:33 4
11244748 654321 2020-04-03T06:20:57 2020-04-03T06:20:57

Tags:  , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Aquí está la consulta SQL modificada que debería darte la salida deseada:

    SELECT
  order_id,
  contact_id,
  placed_at,
  CASE WHEN placed_at < anchor_date THEN ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at) END AS filas_antes_de_ancora,
  CASE WHEN placed_at >= anchor_date THEN ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY placed_at) END AS filas_despues_de_ancora
FROM mytable
ORDER BY contact_id, placed_at

    En la consulta modificada, utilizamos declaraciones CASE para asignar números de fila solo a las filas antes o después de la anchor_date. También ordenamos la salida por contact_id y placed_at para obtener el orden correcto de las filas. Ten en cuenta que para el cálculo de filas_antes_de_ancora, no necesitamos usar el operador < en la cláusula ORDER BY de la función ROW_NUMBER, ya que el orden predeterminado es ascendente. Del mismo modo, para filas_despues_de_ancora, podemos usar >= y el orden ascendente predeterminado.

Comments are closed.