1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| /*pour A et B*/
create table inputs
(item_id serial primary key,
tribe1 numeric not null,
tribe2 numeric not null,
tribe3 numeric not null,
tribe4 numeric not null,
tribe5 numeric not null,
tribe6 numeric not null,
tribe7 numeric not null,
tribe8 numeric not null,
tribe9 numeric not null,
tribe10 numeric not null,
tribe11 numeric not null,
tribe12 numeric not null,
tribe13 numeric not null,
tribe14 numeric not null,
tribe15 numeric not null,
tribe16 numeric not null,
tribe17 numeric not null,
tribe18 numeric not null,
tribe19 numeric not null,
tribe20 numeric not null,
tribe21 numeric not null,
tribe22 numeric not null,
tribe23 numeric not null,
tribe24 numeric not null);
insert into inputs values
(1, 5678543 , 18 , 1 , 667554 , 0 , 98 , 78 , 0 , 19 , 35785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 453 , 1),
(2, 12678543 , 18 , 1 , 37554 , 0 , 98 , 78 , 0 , 19 , 385 , 15 , 612 , 47543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 1),
(3, 678543 , 18 , 1 , 665654 , 0 , 98 , 78 , 0 , 19 , 35785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 10),
(4, 56543 , 18 , 1 , 467554 , 0 , 98 , 78 , 0 , 19 , 3785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 785325 , 9 , 3 , 245 , 51),
(5, 78543 , 18 , 1 , 64554 , 0 , 98 , 78 , 0 , 19 , 358 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 1),
(6, 6778543 , 18 , 1 , 97554 , 0 , 98 , 78 , 0 , 19 , 355785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 251),
(7, 9078543 , 18 , 1 , 554 , 0 , 98 , 78 , 0 , 19 , 5785 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 555 , 9 , 3 , 45 , 1),
(8, 5679543 , 18 , 1 , 90554 , 0 , 98 , 78 , 0 , 19 , 3585 , 15 , 12 , 67543 , 99 , 34 , 6778 , 90 , 1 , 67 , 55325 , 9 , 3 , 45 , 31);
create or replace view coefficient as select
item_id, (tribe1 + tribe2 + tribe3 + tribe4 + tribe5 + tribe6 + tribe7 + tribe8 + tribe9 + tribe10 + tribe11 +
tribe12 + tribe13 + tribe14 + tribe15 + tribe16 + tribe17 + tribe18 + tribe19 + tribe20 + tribe21 + tribe22 +
tribe23 + tribe24)/100 as coefficient from inputs;
create or replace view tribe_coefficient as select
inputs.item_id as item_id,
tribe1 / v.coefficient as tribe1,
tribe2 / v.coefficient as tribe2,
tribe3 / v.coefficient as tribe3,
tribe4 / v.coefficient as tribe4,
tribe5 / v.coefficient as tribe5,
tribe6 / v.coefficient as tribe6,
tribe7 / v.coefficient as tribe7,
tribe8 / v.coefficient as tribe8,
tribe9 / v.coefficient as tribe9,
tribe10 / v.coefficient as tribe10,
tribe11 / v.coefficient as tribe11,
tribe12 / v.coefficient as tribe12,
tribe13 / v.coefficient as tribe13,
tribe14 / v.coefficient as tribe14,
tribe15 / v.coefficient as tribe15,
tribe16 / v.coefficient as tribe16,
tribe17 / v.coefficient as tribe17,
tribe18 / v.coefficient as tribe18,
tribe19 / v.coefficient as tribe19,
tribe20 / v.coefficient as tribe20,
tribe21 / v.coefficient as tribe21,
tribe22 / v.coefficient as tribe22,
tribe23 / v.coefficient as tribe23,
tribe24 / v.coefficient as tribe24,
v.coefficient as coefficient from inputs
join coefficient as v on inputs.item_id = v.item_id;
/* pour C : select from ???? order by ??? |
Partager