sql - operaciones - Valor MÍN. O MÁX. Para rangos de fechas: determinación del precio más bajo para un rango de fechas determinado en función de los rangos de ID de producto, precio y fecha

rango de fechas sql

Question

Realmente espero que a algunos de ustedes les gusten los desafíos. Tengo una tabla de códigos de producto, precios y rangos de fechas para cuando esos precios estén activos.

+----+-------+---------------------+---------------------+
| Id | Price | StartDate | EndDate |
+----+-------+---------------------+---------------------+
| 1 | 19 | 2016-12-01 00:00:00 | 2017-12-01 23:59:59 |
| 1 | 18 | 2017-01-01 00:00:00 | 2018-01-12 23:59:59 |
| 1 | 17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 |
| 1 | 16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 |
| 2 | 15 | 2017-01-01 00:00:00 | 2017-03-05 23:59:59 |
| 2 | 15 | 2017-03-06 00:00:00 | 2017-03-31 23:59:59 |
| 2 | 30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 |
| 3 | 12 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
| 3 | 12 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 |
| 4 | 14 | 2017-01-01 00:00:00 | 2017-04-05 23:59:59 |
| 4 | 14 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
| 4 | 12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 |
| 5 | 20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
| 5 | 20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 |
| 6 | 15 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
| 6 | 15 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 |
| 6 | 15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
+----+-------+---------------------+---------------------+

SQLFiddle: http://sqlfiddle.com/#!6/39288/1

Necesito obtenerlo en un formato donde:

1. Los períodos de fecha tienen el mismo ID y precio que "touch" (es decir, Id. N.º 3) se fusionan en un período.

2. Los períodos de fechas que se superponen (es decir, Id. N.º 4) se fusionan en un período.

3. El precio más bajo se muestra para cada producto y durante qué rango.

4. Los intervalos de fechas que tienen espacios vacíos y el mismo precio no se fusionan y son filas separadas (es decir, Id. N.º 5).

El resultado debería ser:

+----+-------+---------------------+---------------------+
| Id | Price | StartDate | EndDate |
+----+-------+---------------------+---------------------+
| 1 | 19 | 2016-12-01 00:00:00 | 2016-12-31 23:59:59 |
| 1 | 18 | 2017-01-01 00:00:00 | 2017-02-02 23:59:59 |
| 1 | 17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 |
| 1 | 19 | 2017-03-04 00:00:00 | 2017-12-01 23:59:59 |
| 1 | 18 | 2017-12-02 00:00:00 | 2017-12-31 23:59:59 |
| 1 | 16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 |
| 2 | 15 | 2017-01-01 00:00:00 | 2017-03-31 23:59:59 |
| 2 | 30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 |
| 3 | 12 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 |
| 4 | 14 | 2017-01-01 00:00:00 | 2017-04-14 23:59:59 |
| 4 | 12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 |
| 5 | 20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 |
| 5 | 20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 |
| 6 | 15 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 |
| 6 | 15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 |
+----+-------+---------------------+---------------------+

En general, es esencialmente determinar el mejor precio entre dos fechas.

He trabajado con esta tabla en el pasado y pude resolverla en C #, pero esta vez necesito un enfoque puro de TSQL.

Ya he bajado algunos CTE anidados profundos y he perdido la cabeza con resultados que no están ni cerca de lo que deberían ser. Gracias de antemano por cualquier persona que pueda ayudar.

Editar: Incluso arruiné los resultados deseados porque esto es muy confuso. Reparado (creo).

Editar 2: Ejemplo:

+------+-------+-------------------------+-------------------------+
| Id | Price | StartDate | EndDate |
+------+-------+-------------------------+-------------------------+
| 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-09-24 23:59:59.000 |
| 8611 | 31.98 | 2017-09-25 00:00:00.000 | 2017-12-31 23:59:59.000 |
| 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 |
| 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

Los resultados de @ GordonLinoff a:

+------+-------+-------------------------+-------------------------+
| Id | Price | StartDate | EndDate |
+------+-------+-------------------------+-------------------------+
| 8611 | 28.78 | 2017-06-06 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

El resultado debería ser:

+------+-------+-------------------------+-------------------------+
| Id | Price | StartDate | EndDate |
+------+-------+-------------------------+-------------------------+
| 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-07-30 23:59:59.000 |
| 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 |
| 8611 | 31.98 | 2017-10-01 00:00:00.000 | 2017-10-29 23:59:59.000 |
| 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+

Answer 1

¿Tiene una tabla de calendario / fecha disponible? Si es así, puede usar la tabla de fechas para ayudarlo a obtener el precio mínimo por producto para cada fecha dentro de los períodos de su tabla.

Posteriormente, puede obtener las fechas de inicio y finalización de cada uno de sus períodos mirando los registros siguiente y anterior con el mismo ID de producto. Puede usar las funciones LAG y LEAD para hacer esto. Esto te da los límites exteriores de cada uno de tus grupos deseados.

A partir de ahí, es solo un poco de tiddling alrededor para obtener su resultado final. He proporcionado un ejemplo a continuación, que debería darle los resultados que desea.

--Get the best price per date for each product
WITH BestPricePerDate AS (
SELECT
Id,
MIN(Price) Price,
c.[Date]
FROM [YourTable] yt
INNER JOIN dbo.Calendar c
ON c.[Date] BETWEEN yt.StartDate AND yt.EndDate
GROUP BY Id, [Date]
),
--Check whether the date is the start or the end of a period
PeriodsMarkedPerId AS(
SELECT
Id,
Price,
[Date],
CASE WHEN
ISNULL(LAG(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price
OR ISNULL(LAG([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),''1999-01-01'') <> DATEADD(DAY,-1,[Date]) THEN 1 ELSE 0 END IsStartDate,
CASE WHEN
ISNULL(LEAD(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price
OR ISNULL(LEAD([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),''1999-01-01'') <> DATEADD(DAY,1,[Date]) THEN 1 ELSE 0 END IsEndDate
FROM BestPricePerDate
),
--Keep only the start and end date records
PeriodStartAndEndDates AS(
SELECT
Id,
Price,
[Date],
IsStartDate,
IsEndDate
FROM PeriodsMarkedPerId
WHERE IsStartDate = 1 OR IsEndDate = 1
),
--Move StartDate and EndDate to one record
StartAndEndDatesOnSameRow AS(
SELECT
Id,
Price,
[Date] AS StartDate,
LEAD([Date],1) OVER (ORDER BY Id, [Date]) AS EndDate,
IsStartDate
FROM PeriodStartAndEndDates
)
--Get the resulting periods
SELECT Id, Price, StartDate, EndDate
FROM StartAndEndDatesOnSameRow
WHERE IsStartDate = 1
ORDER BY Id, StartDate

Si no tienes una tabla de fechas, entonces puedes crear una fácilmente. Hay muchos ejemplos de esto en la web.

¡Espero que esto ayude!

Answer 2

Puede definir el comienzo de un período como uno que no se superpone. Eso es complicado, pero se puede hacer usando exists o un máximo acumulativo de la fecha de finalización excluyendo la fila actual.

Entonces, cada no superposición es el comienzo de un grupo. Ese grupo se puede usar para la agregación:

select id, min(startDate) as startDate, max(endDate) as endDate, min(price) as price
from (select t.*,
sum(case when prev_endDate < dateadd(second, -1, startDate)
then 1 else 0
end) over (partition by id order by startdate) as grp
from (select t.*,
max(endDate) over (partition by id
order by startdate
rows between unbounded preceding and 1 preceding
) as prev_endDate
from t
) t
) t
group by id, grp;

No estoy 100% seguro de que esto funcione. Solo pensé en usar la fecha de finalización acumulativa máxima para esto. Estoy bastante seguro de que cubre todos los casos superpuestos, pero podría haberme perdido algo.