En la tabla de personal de SQL, hemos almacenado un valor de tasa flotante que cambia con el tiempo. Columnas:

id (serial, PK)
person_id (int)
date_from (date)
rate (float)

(person_id, date_from) es único, porque a lo sumo se permite un cambio por día (tal vez podría ser un PK, pero no es importante)

El valor de la tasa para un person_id dado es válido en el dominio de tiempo desdedate_from fecha para el siguiente registro con éxitodate_from, o al futuro infty si no existe tal registro. Ejemplo:

id   person_id  date_from  rate
101  1          2011-01-01 100.0
145  1          2012-01-01 180.0
193  1          2012-05-01 140.0

Ahora necesito una consulta SELECT que para cadaperson_id devuelve unrate válido para alguna fecha dada $. Digamos que para el 2012-03-01 es 180; para el 2012-05-02 es 140 y así.

Soluciones que he probado:

1) condicióndate_from <= $date + usar la función de ventanarank() OVER (PARTITION BY person_id ORDER BY date_from DESC + en superselectWHERE rank = 1

2) similar a 1) pero usaSELECT DISTINCT ON (person_id) en lugar de limitar el rango a 1

Tanto 1) como 2) no funcionan bien, EXPLICAR muestra que db necesita clasificar todos los registros para cada unoperson_id y luego limitar a 1 primero. Probablemente este tipo de consulta no puede utilizar completamente el índice endate_from?

IDEA - Agrega undate_to columna, que será un poco redundante, porque el valor será "date_from of sucestive record, minus 1 day" (o + infty si no hay un registro sucesivo). Pero la consulta entonces podría ser condate_from <= $date AND date_to >= $date - lo que probablemente tendría un buen desempeño con índices en date_from y date_to.

Pero tengo un poco de miedo de cómo administrar la integridad de los datos en este caso, ¿cómo configurar la restricción de que [date_from .. date_to] los intervalos para una persona_id no se deben superponer?

¿Cuál es la mejor solución para postgresql para este tipo de consulta? La carga se lee más, no se escribe mucho en la tabla person_rate. Las consultas típicas necesitarían internamente obtener una tarifa por cada día del mes ...

Tal vez estoConsulta SQL para ordinal de índice / clave primaria ¿Con los nuevos índices en la página 9.2 puede ayudar de alguna manera?