首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >与组错误相关的数据仓库错误

问与组错误相关的数据仓库错误
EN

Stack Overflow用户
提问于 2021-01-29 16:28:19
回答 1查看 31关注 0票数 0

因此,我有2个tables:Clients_Test和Vacations_Test.I需要创建一个包含两个维度的仓库: d1_month和d2_destination.The主表,应该包含这两个维度的字段是事实table.Also,事实需要多两个字段:每个客户的总目的地+总价。问题是,在我将数据插入事实表之后,组按行分解,我不知道why.Tried可以使用其他字段对它们进行分组,但它仍然使我望而却步。

代码语言:javascript
复制
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
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-01-29 19:14:41

这并不是因为GROUP BY失败,而是因为主键被违反了:

代码语言:javascript
复制
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
  • ,然后确保查询返回两个主键列的唯一组合:[nr_vacations, total_price]
    • 如何?也许通过将where子句包含到查询中,或者.谁知道呢?我不知道,但你should.
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/65958220

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档