Consulta de SQL Server que devuelve los precios de cada tienda en cada fecha y agrega un 0 si no hay datos disponibles para el shopId en una fecha determinada.

05 Aug, 2023 Programación 0

Tengo esta tabla de base de datos de Sql Server llamada productPrices:

shopId int 
price decimal(18, 2)
dateFound datetime

Quiero hacer una consulta SQL formateada para usar en un gráfico de líneas de chart.js. El gráfico de líneas toma los siguientes parámetros:

Un arreglo de valores de X, que en este contexto será la fecha/día (de menor a mayor).
Cada shopId contiene un arreglo de precios.

He creado una versión de CodePen de cómo los datos deben mostrarse en el gráfico de líneas (usando solo datos agregados manualmente).

El problema es que si no se agregó ningún precio para la tienda en un día en particular, aún debe haber un valor de datos, por ejemplo, 0, presente, ya que la longitud del arreglo de precios debe coincidir con el arreglo de valores de X.

¿Cómo podría hacer una consulta que complete con 0 cuando el shopId en particular no tenga un valor en esa fecha en particular?

¿Cuál es la mejor manera de formatear los datos devueltos por la consulta SQL cuando se deben usar para la estructura que utiliza el gráfico de líneas de chart.js?

SELECT dateFound AS xValue, COALESCE(price, 0) AS yValue, shopId FROM (SELECT DISTINCT dateFound FROM productPrices) dates CROSS JOIN (SELECT DISTINCT shopId FROM productPrices) shops LEFT JOIN productPrices ON dates.dateFound = productPrices.dateFound AND shops.shopId = productPrices.shopId ORDER BY dateFound ASC, shopId ASC

{ labels: ['2019-01-01', '2019-01-02', '2019-01-03', ...], // array de fechas datasets: [ { label: 'Tienda 1', data: [10, 0, 15, ...] // array de precios, incluyendo 0 para los valores faltantes }, { label: 'Tienda 2', data: [7, 13, 0, ...] }, ... ] }

Answer

davy.ai

August 5, 2023 at 2:50 am
Para rellenar los datos faltantes con ceros, podemos usar un JOIN IZQUIERDO para crear una tabla con todas las posibles combinaciones de shopId y dateFound, y luego utilizar COALESCE para reemplazar cualquier valor NULL por 0:
```
SELECT 
  dateFound AS xValue,
  COALESCE(price, 0) AS yValue,
  shopId
FROM 
  (SELECT DISTINCT dateFound FROM productPrices) dates
  CROSS JOIN 
  (SELECT DISTINCT shopId FROM productPrices) shops
  LEFT JOIN 
  productPrices ON dates.dateFound = productPrices.dateFound AND shops.shopId = productPrices.shopId
ORDER BY
  dateFound ASC,
  shopId ASC
```
Esta consulta nos proporciona una tabla que se puede mapear fácilmente a la estructura que utiliza el gráfico de líneas de chart.js, donde cada fila representa un punto en el gráfico:
```
{
  labels: ['2019-01-01', '2019-01-02', '2019-01-03', ...], // array de fechas
  datasets: [
    {
      label: 'Tienda 1',
      data: [10, 0, 15, ...] // array de precios, incluyendo 0 para los valores faltantes
    },
    {
      label: 'Tienda 2',
      data: [7, 13, 0, ...]
    },
    ...
  ]
}
```

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Consulta de SQL Server que devuelve los precios de cada tienda en cada fecha y agrega un 0 si no hay datos disponibles para el shopId en una fecha determinada.

Answer

davy.ai