Ошибка хранилища данных, ошибочно связанная с группой

Итак, у меня есть 2 таблицы: Clients_Test и Vacations_Test. Мне нужно создать склад с двумя измерениями: d1_month и d2_destination. Основная таблица, которая должна содержать поля этих двух измерений, является таблицей фактов. поля: общее количество направлений каждого клиента + общая цена. Проблема в том, что после того, как я вставляю данные в таблицу фактов, группировка по строкам прерывается, и я не знаю почему. Пытался сгруппировать их, используя другие поля, но все равно это удерживает меня.

CREATE TABLE CLIENTS_TEST(
    IDclient NUMBER(7) PRIMARY KEY,
    name_client VARCHAR2(40) ,
    adress VARCHAR2(50),
    city VARCHAR2(30),
    county VARCHAR2(20));

CREATE TABLE VACATIONS_TEST(
    number_contract NUMBER(6) PRIMARY KEY,
    IDclient NUMBER(7) REFERENCES CLIENTI_TEST(IDclient),
    date_contract DATE,
    destination VARCHAR2(40),
    price NUMBER(10));

INSERT INTO CLIENTS_TEST VALUES (1, 'name1','adr1', 'Timisoara', 'Timis');
INSERT INTO CLIENTS_TEST VALUES (2, 'name2','adr2', 'Arad', 'Arad');
INSERT INTO CLIENTS_TEST VALUES (3, 'name3','adr3', 'Cluj', 'Cluj');
INSERT INTO CLIENTS_TEST VALUES (4, 'name4','adr4', 'Arad', 'Arad');
INSERT INTO CLIENTS_TEST VALUES (5, 'name5','adr5', 'Timisoara', 'Timis');
INSERT INTO CLIENTS_TEST VALUES (6, 'name6','adr6', 'Cluj', 'Cluj');

INSERT INTO VACATIONS_TEST VALUES (11, 2, TO_DATE('2/05/2020','dd/mm/yyyy'), 'dest2', 4000);
INSERT INTO VACATIONS_TEST VALUES (12, 3, TO_DATE('4/05/2020','dd/mm/yyyy'), 'dest5', 4000);
INSERT INTO VACATIONS_TEST VALUES (13, 6, TO_DATE('8/05/2020','dd/mm/yyyy'), 'dest3', 3000);
INSERT INTO VACATIONS_TEST VALUES (14, 1, TO_DATE('10/05/2020','dd/mm/yyyy'), 'dest4', 3000);
INSERT INTO VACATIONS_TEST VALUES (15, 3, TO_DATE('12/05/2020','dd/mm/yyyy'), 'dest1', 2000);
INSERT INTO VACATIONS_TEST VALUES (16, 5, TO_DATE('15/05/2020','dd/mm/yyyy'), 'dest2', 4000);
INSERT INTO VACATIONS_TEST VALUES (17, 2, TO_DATE('18/05/2020','dd/mm/yyyy'), 'dest4', 5000);
INSERT INTO VACATIONS_TEST VALUES (18, 6, TO_DATE('21/05/2020','dd/mm/yyyy'), 'dest5', 3000);
INSERT INTO VACATIONS_TEST VALUES (19, 1, TO_DATE('24/05/2020','dd/mm/yyyy'), 'dest1', 4000);
INSERT INTO VACATIONS_TEST VALUES (20, 4, TO_DATE('27/05/2020','dd/mm/yyyy'), 'dest3', 6000);
INSERT INTO VACATIONS_TEST VALUES (21, 3, TO_DATE('3/06/2020','dd/mm/yyyy'), 'dest5', 4000);
INSERT INTO VACATIONS_TEST VALUES (22, 4, TO_DATE('6/06/2020','dd/mm/yyyy'), 'dest2', 3000);
INSERT INTO VACATIONS_TEST VALUES (23, 6, TO_DATE('7/06/2020','dd/mm/yyyy'), 'dest4', 6000);
INSERT INTO VACATIONS_TEST VALUES (24, 2, TO_DATE('9/06/2020','dd/mm/yyyy'), 'dest5', 5000);
INSERT INTO VACATIONS_TEST VALUES (25, 5, TO_DATE('11/06/2020','dd/mm/yyyy'), 'dest2', 4000);
INSERT INTO VACATIONS_TEST VALUES (26, 3, TO_DATE('14/06/2020','dd/mm/yyyy'), 'dest1', 3000);
INSERT INTO VACATIONS_TEST VALUES (27, 2, TO_DATE('17/06/2020','dd/mm/yyyy'), 'dest4', 6000);
INSERT INTO VACATIONS_TEST VALUES (28, 6, TO_DATE('19/06/2020','dd/mm/yyyy'), 'dest5', 4000);
INSERT INTO VACATIONS_TEST VALUES (29, 1, TO_DATE('21/06/2020','dd/mm/yyyy'), 'dest3', 2000);
INSERT INTO VACATIONS_TEST VALUES (30, 5, TO_DATE('27/06/2020','dd/mm/yyyy'), 'dest4', 3000);
INSERT INTO VACATIONS_TEST VALUES (31, 4, TO_DATE('1/07/2020','dd/mm/yyyy'), 'dest5', 3000);
INSERT INTO VACATIONS_TEST VALUES (32, 3, TO_DATE('4/07/2020','dd/mm/yyyy'), 'dest1', 2000);
INSERT INTO VACATIONS_TEST VALUES (33, 1, TO_DATE('6/07/2020','dd/mm/yyyy'), 'dest3', 2000);
INSERT INTO VACATIONS_TEST VALUES (34, 4, TO_DATE('10/07/2020','dd/mm/yyyy'), 'dest1', 3000);
INSERT INTO VACATIONS_TEST VALUES (35, 6, TO_DATE('12/07/2020','dd/mm/yyyy'), 'dest2', 4000);
INSERT INTO VACATIONS_TEST VALUES (36, 5, TO_DATE('15/07/2020','dd/mm/yyyy'), 'dest3', 3000);
INSERT INTO VACATIONS_TEST VALUES (37, 4, TO_DATE('22/07/2020','dd/mm/yyyy'), 'dest4', 2000);
INSERT INTO VACATIONS_TEST VALUES (38, 2, TO_DATE('24/07/2020','dd/mm/yyyy'), 'dest1', 4000);
INSERT INTO VACATIONS_TEST VALUES (39, 1, TO_DATE('27/07/2020','dd/mm/yyyy'), 'dest5', 2000);
INSERT INTO VACATIONS_TEST VALUES (40, 5, TO_DATE('29/07/2020','dd/mm/yyyy'), 'dest4', 4000);



Create  table d1_month (month_contract number(2) primary key);
Insert into d1_month select distinct extract(month from data_contract) from vacations_test;


Create table d2_destination(destination varchar2(40) Primary key);
insert into d2_destination select distinct destination from vacations_test;

Create table fact (month_contract number(2) references d1_month(month_contract),destination varchar2(40) references d2_destination(destination),
nr_vacations number(10),total_price number(20),Primary key(nr_vacations,total_price));



Insert into fact
select extract(month from VACATIONS_TEST.date_contract), VACATIONS_TEST.destination,count(VACATIONS_TEST.IDclient),
sum(VACATIONS_TEST.price) 
from vacations_test, clients_test
WHERE VACATIONS_TEST.IDclient=CLIENTS_TEST.IDclient
group by VACATIONS_TEST.destination, extract(month from VACATIONS_TEST.date_contract); // error

oracle warehouse
person Cristi    schedule 29.01.2021    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Дело не в том, что GROUP BY не работает, а в том, что первичный ключ нарушается:

SQL> insert into fact (month_contract, destination, nr_vacations, total_price)
  2  select
  3    extract(month from vacations_test.date_contract),
  4    vacations_test.destination,
  5    count(vacations_test.idclient),
  6    sum(vacations_test.price)
  7  from vacations_test, clients_test
  8  where vacations_test.idclient = clients_test.idclient
  9  group by vacations_test.destination,
 10  extract(month from vacations_test.date_contract)
 11  ;
insert into fact (month_contract, destination, nr_vacations, total_price)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C007160) violated


SQL>

Что делать?

  • если fact первичный ключ таблицы установлен неправильно, измените его
  • if primary key is OK but data violate it, then make sure that query returns unique combination of the two primary key columns: [nr_vacations, total_price]
    • how? Maybe by including the where clause to the query, or ... who knows? I don't, but you should.
person Littlefoot    schedule 29.01.2021