¿Cómo obtener el primer y último evento relacionado sin tener que hacer subconses adicionales? -- sql-server campo con tsql camp Relacionados El problema

How to get first and last related event without having to do extra subqueries?


1
vote

problema

Español

Tengo dos tablas: select case when `myProcedure`(1315)=1 then 1 else 1315 end; 5 y select case when `myProcedure`(1315)=1 then 1 else 1315 end; 6 . Cada cliente puede tener 0 o más eventos.

Estoy trabajando en un informe que se supone que debe mirar algo así:

  select case when `myProcedure`(1315)=1 then 1 else 1315 end; 7  

La consulta real será mucho más grande que eso, pero esta es la esencia general de la misma.

Si solo necesitaba las fechas, podría hacerlo fácilmente usando agregados:

  select case when `myProcedure`(1315)=1 then 1 else 1315 end; 8  

Desafortunadamente, eso deja a los mensajes correspondientes.

He logrado resolverlo usando una subconsulta para cada mensaje, pero eso lo obtiene> super caro, así que me preguntaba si hay otro enfoque que sugieres. La consulta completa es más compleja de lo que el ejemplo tendrá uniones en diferentes tipos de eventos, cada uno mostrando la fecha y el mensaje del primer y último evento.

Original en ingles

I have two tables: Customers and CustomerEvents. Each customer can have 0 or more events.

I'm working on a report that's supposed to look something like this:

CustomerName     | FirstEventDate | FirstEventMessage   | LastEventDate | LastEventText --------------------------------------------------------------------------------------- Customers 'R' US | 2018-01-01     | Customer registered | 2018-04-06    | Customer Quit 

The actual query is going to be a lot larger than that, but this is the general gist of it.

If I only needed the dates, I could easily do it using aggregates:

SELECT      c.Name AS ContactName     , MAX(e.DateTime) AS FirstEventDate     , '???' AS FirstEventMessage     , MIN(e.DateTime) AS FirstEventDate     , '???' AS FirstEventMessage FROM Contacts c     LEFT JOIN CustomerEvents e ON e.ContactId = c.Id GROUP BY c.Name 

Unfortunately, that leaves out the corresponding messages.

I've managed to solve it using a subquery for each message, but that gets super expensive, so I was wondering if there's another approach you'd suggest. The full query is more complex than the example will have joins on different kinds of events, each displaying the date and message of the first and last event.

     
         
         

Lista de respuestas

2
 
vote
vote
La mejor respuesta
 

Como me preguntaste, para crear una respuesta de mi comentario, aquí estás:

con la versión 2012 Microsoft presentó algunas nuevas funciones de ventana. Útil para usted es FIRST_VALUE() y LAST_VALUE() . Ambos necesitan una cláusula OVER() para especificar el orden de clasificación y, si es necesario, una regla de partición.

Esto debería funcionar (pero no conozco sus tablas y datos):

  SELECT      c.Name AS ContactName     , MIN(e.DateTime) AS FirstEventDate     , FIRST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS FirstEventMessage     , MAX(e.DateTime) AS LastEventDate     , LAST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS LastventMessage FROM Contacts c     LEFT JOIN CustomerEvents e ON e.ContactId = c.Id GROUP BY c.Id,c.Name;   

Pero ten cuidado: si su e.DateTime6 no es único (por Contact ), obtendrá un valor aleatorio "Primer" ...

sugerencias

  • Asegúrese de tener índices en DateTime y ContactId y
  • Agregue el str = 'a_b_c_d_e' # Remainder str[0...-2] # -> 'a_b_c_d' # Last symbol str[-1] # -> 'e' 0 a su str = 'a_b_c_d_e' # Remainder str[0...-2] # -> 'a_b_c_d' # Last symbol str[-1] # -> 'e' 111

Una alternativa fue reemplazar el str = 'a_b_c_d_e' # Remainder str[0...-2] # -> 'a_b_c_d' # Last symbol str[-1] # -> 'e' 2 con una subricería correlacionada ejecutada en filas ejecutadas. Esto tiene la ventaja, que puede estar seguro de que los valores (y más) se toman de la misma fila.

  str = 'a_b_c_d_e' # Remainder str[0...-2]    # -> 'a_b_c_d'  # Last symbol str[-1]        # -> 'e' 3  

luego use las columnas en su consulta como

  str = 'a_b_c_d_e' # Remainder str[0...-2]    # -> 'a_b_c_d'  # Last symbol str[-1]        # -> 'e' 4  
 

As you asked me, to create an answer out of my comment, here you are:

With version 2012 Microsoft introduced some new windowing functions. Usefull for you are FIRST_VALUE() and LAST_VALUE(). Both need an OVER() clause to specify the sort order and - if needed - a partitioning rule.

This should work (but I do not know your tables and data):

SELECT      c.Name AS ContactName     , MIN(e.DateTime) AS FirstEventDate     , FIRST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS FirstEventMessage     , MAX(e.DateTime) AS LastEventDate     , LAST_VALUE(e.EventMessage) OVER(ORDER BY e.DateTime) AS LastventMessage FROM Contacts c     LEFT JOIN CustomerEvents e ON e.ContactId = c.Id GROUP BY c.Id,c.Name; 

But be warned: If your e.DateTime is not unique (per Contact), you will get a random "first" value...

Hints

  • Make sure to have indexes on DateTime and ContactId and
  • add the c.Id to your GROUP BY

An alternative was to replace the LEFT JOIN CustomerEvents with a row-wise executed correlated sub-query. This has the advantage, that you can be sure, that both (and more) values are taken from the same row.

OUTER APPLY (SELECT TOP 1 e.[DateTime],e.[EventMessage]                FROM CustomerEvents AS e              WHERE e.ContactId=c.Id              ORDER BY e.EventMessage ASC) AS FirstEvent --same with DESC OUTER APPLY (SELECT TOP 1 e.[DateTime],e.[EventMessage]                FROM CustomerEvents AS e              WHERE e.ContactId=c.Id              ORDER BY e.EventMessage DESC) AS LastEvent 

Then use the columns in your query like

 FirstEvent.DateTime AS FirstDateTime ,FirstEvent.EventMessage AS FirstMessage ,LastEvent.DateTime AS LastDateTime ,LastEvent.EventMessage AS LastMessage 
 
 
0
 
vote

Pruebe este enfoque CTE, ajuste las columnas para seleccionar la cláusula de acuerdo con usted.

  str = 'a_b_c_d_e' # Remainder str[0...-2]    # -> 'a_b_c_d'  # Last symbol str[-1]        # -> 'e' 5  
 

Try this CTE approach, adjust the columns to SELECT clause according to you need.

;WITH CTE AS(     SELECT          *,          ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY e.DateTime) AS RowAsc,         ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY e.DateTime DESC) AS RowDesc     FROM Contacts c         LEFT JOIN CustomerEvents e ON e.ContactId = c.Id     ) SELECT      c1.*     , c2.* FROM CTE c1 INNER JOIN CTE c2 ON c1.Name = c2.Name     AND c1.RowAsc = 1      AND c2.RowDesc = 1 
 
 
0
 
vote

intente por debajo del enfoque

  str = 'a_b_c_d_e' # Remainder str[0...-2]    # -> 'a_b_c_d'  # Last symbol str[-1]        # -> 'e' 6  
 

Try Below Approach

SELECT      c.Name AS CustomerName     , MAX(e.DateTime) AS LastEventDate     , B.Message AS FirstEventMessage     , MIN(e.DateTime) AS FirstEventDate     , D.Message AS FirstEventMessage FROM #Customers c     LEFT JOIN #CustomerEvents e ON e.CustomerId = c.Id     LEFT JOIN      (     SELECT A.CustomerId, A.Message     FROM         (         SELECT              CustomerId, Message, Row_Number()over(Partition By CustomerId order by DATETIME DESC) as No         FROM             #CustomerEvents          ) A     WHERE         A.No = 1     ) B ON B.CustomerId = C.Id     LEFT JOIN      (     SELECT A.CustomerId, A.Message     FROM         (         SELECT              CustomerId, Message, Row_Number()over(Partition By CustomerId order by DATETIME) as No         FROM             #CustomerEvents          ) A     WHERE         A.No = 1     ) d ON d.CustomerId = C.Id GROUP BY      c.Name, B.Message, D.Message 
 
 

Relacionados problema

1  Transposición de una tabla con SQL  ( Transposing a table with sql ) 
Tengo una tabla SQL como SO Área PV COUTERPARTY 851 M010 Name1 561 M011 Name2 869 M012 Name3 ... y necesito transportarlo, utilizando T-SQL (no inform...

49  SQL Server equivalente a los nulos de Oracle primero?  ( Sql server equivalent to oracles nulls first ) 
Para que Oracle tiene nulos primero, lo que puedo usar para que los valores nulos se clasificaron en la parte superior seguidos por mi valor de columna en ord...

501  ¿Cómo divido una cadena para poder acceder a item x?  ( How do i split a string so i can access item x ) 
Uso de SQL Server, ¿cómo divido una cadena para que pueda acceder a item x? Toma una cadena "Hola, John Smith". ¿Cómo puedo dividir la cadena por espacio y ...

0  SQL Server 2005: ¿Cómo crear un TSQL según mi lógica de negocios?  ( Sql server 2005 how to create a tsql as per my business logic ) 
Tengo dos tablas: - Table_01 table01id (int) testid (unideidentifier) ​​ Testdate (DateTime) testno (varchar) Table_02 table02id (int) testi...

1  Charindex en SQL no da el resultado deseado  ( Charindex in sql doesnt give the desired result ) 
Tengo una cadena que es una salida de una función, por ejemplo: "1,3,16, ..." .. ". Utilicé la siguiente consulta SQL y lo ejecuté en el constructor de cons...

3  SQL se une y exclusiones  ( Sql joins and exclusions ) 
asumiendo una consulta como tal en T-SQL. SELECT * FROM Stock S LEFT JOIN StockBarcode SB ON SB.StockID = S.StockID ...

0  O / RM Elección (no depende de Linq)  ( O rm choice not reliant on linq ) 
Me gusta usar SQL, y creo que soy bastante bueno con eso. Y traté de usar LINQ (con C # y Linq2SQL, parece que se siente más similar a SQL con vb.net), sien...

4  SQL Server: seleccione Todos los elementos con una fecha en el mes anterior  ( Sql server select all items with a date on the previous month ) 
Tengo una tabla en mi base de datos de SQL Server llamada " los elementos " que tiene una columna llamada " datifinished ". Tengo un script que se ejecutará...

3  Error de sintaxis en el procedimiento almacenado al pasar la cadena que contiene espacio  ( Syntax error in stored procedure when passing string containing space ) 
Cuando pase List11111111. Con los valores separados en el espacio, como List2 , hay un error de sintaxis. Si no hay espacios en el medio, por ejemplo, Lis...

3  TSQL Dynamic Advinga de columnas en el procedimiento almacenado  ( Tsql dynamic adding of columns in stored procedure ) 
Hola. Estoy escribiendo un gran procedimiento almacenado, que crea una tabla de informes dinámicos, de las columnas de N en tamaño, los primeros 6 son constan...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos