Классическая задача для финансового сектора на справочник с историей изменений )) Это когда некий объект БД во внешних ссылках идентифицируется не по ID(номеру строки) текущей записи в справочнике, а по коду, один раз присвоенному при создании этого объекта.
- Код: Выделить всё
table 'Staff' -> id / code / fio / date_activ / date_deactiv
--------------------------------------------
1 1 Pupkin 01.01.2024 01.05.2024
2 1 Pupkin 01.05.2024 01.01.2025
3 1 Pupkin 01.01.2025 null -> текущая активная запись
- Код: Выделить всё
table 'Salary' -> id / code / staff_code / salary /date_activ / date_deactiv
staff_code -> ссылка на 'Staff.code'
--------------------------------------------
1 1 1 1000.00 01.01.2024 01.05.2025
2 1 1 100000.00 01.05.2025 null -> текущая активная запись
В таком случае данные из участвующих в запросах таблиц нужно отбирать по условию 'date_activ <= <some_date> and (date_deаctiv is null or date_deactiv > <some_date>)', в случае нескольких удовлетворяющих записей выбрать среди них запись с последней (по дате) 'date_activ'. Это может показаться громоздким в случае много-табличных запросов, но все становится просто, если БД поддерживает VIEWшки, а еще лучше (наприклад PostgreSQL) - функции, принимающие аргументы (например <some_date>) и возвращающие VIEWшки ))
- Код: Выделить всё
CREATE TABLE ref_suppl_index (
id SERIAL4 NOT NULL,
code INTEGER NOT NULL,
name INTEGER NOT NULL, /*индекс*/
company_code INTEGER NOT NULL,
catalog_code INTEGER NOT NULL,
indexes_ss INTEGER[],
indexes_retail INTEGER[],
date_input DATE,
date_activ DATE,
date_deactiv DATE,
oper_code INTEGER,
rec_state INTEGER
);
CREATE UNIQUE INDEX ref_suppl_index_pkey ON ref_suppl_index (id);
CREATE UNIQUE INDEX ref_suppl_index_code_uidx ON ref_suppl_index (code,date_activ) WHERE date_deactiv IS NULL OR date_deactiv > date_deactiv;
CREATE UNIQUE INDEX ref_suppl_index_name_uidx ON ref_suppl_index (company_code,catalog_code,name) WHERE date_deactiv IS NULL OR date_deactiv > date_deactiv;
CREATE INDEX ref_suppl_index_da_idx ON ref_suppl_index (date_activ);
CREATE INDEX ref_suppl_index_dda_idx ON ref_suppl_index (date_deactiv);
CREATE OR REPLACE VIEW "v_suppl_index" AS SELECT
id,
code,
name, /*индекс*/
company_code,
catalog_code,
indexes_ss,
indexes_retail
FROM ref_suppl_index;
----------------------------
CREATE OR REPLACE FUNCTION "f_suppl_index"(date_activ date) RETURNS SETOF "int4" AS $BODY$
select
max(a.id)
from
ref_suppl_index a
inner join (
select
max(date_activ) as date_activ,
code
from
ref_suppl_index
where
($1 >= date_activ)
and
(($1 < date_deactiv) or date_deactiv is null)
group by code
) b
using
(code,date_activ)
group by
a.code;
$BODY$ LANGUAGE 'sql' VOLATILE COST 100 ROWS 1000;
----------------------
CREATE OR REPLACE FUNCTION "fv_suppl_index"(date_activ date) RETURNS SETOF "v_suppl_index" AS $BODY$
select
id,
code,
name, /*индекс*/
company_code,
catalog_code,
indexes_ss,
indexes_retail
from
ref_suppl_index a1 inner join f_suppl_index($1) a2 on a1.id=a2
$BODY$ LANGUAGE 'sql' VOLATILE COST 100 ROWS 1000;
Использование в запросах - например данные, актуальные на дату 2024-11-03 :
- Код: Выделить всё
SELECT si.code AS si_code, si.name FROM fv_suppl_index('2024-11-03'::date) si;
Для упрощения статистических выборок рекомендуется ( и плевать на строгую реляционность ! ) копировать используемые в статистике данные из справочников в рабочие таблицы при вставке в них - чтобы позже не нужно было обращаться к справочникам ))
Если БД не поддерживает такие VIEWшки, то приходится городить дурацкий огород с открытием/закрытием рабочего дня - там для кажого справочника согласно анализа "date_activ" и "date_deactiv" создается дополнительная таблица маппинга "code ->id', перезаполняемая при открытии каждого нового дня ))
