1
votes

Comment attribuer des méthodes à un paramètre spécifique à l'aide de PIVOT dans SQL?

Nous travaillons à la production de nos données sous la forme d'une base de données / d'un ensemble relationnel et d'un ensemble de données ligne-colonne (afin que les utilisateurs des données puissent utiliser le format qui leur convient le mieux). Voici un tableau simplifié de nos données:

drop table parameter_pivot;
create table parameter_pivot
 (
  site_id       VARCHAR(30),
  ANC           NUMBER,
  NO3           NUMBER,
  ANC_method    VARCHAR(30),
  NO3_method    VARCHAR(30)
);

insert into parameter_pivot values ('site1', 10.1, 2.1, 'Gran 1952', 'EPA 1983');
insert into parameter_pivot values ('site2', 21.8, 7.2, 'Gran 1952', 'EPA 1983');

Je sais pivoter pour les valeurs, mais cela ne met pas la méthode dans une colonne_paramètre spécifique (méthode ANC_method ou NO3): p >

drop table parameter_pivot_test;
create table parameter_pivot_test
AS
(select * from (select site_id, parameter_number, parameter
   from parameter_test) 
   pivot (sum(parameter_number) for parameter in (
'ANC'   ANC,
'NO3'   NO3
)))
;

Mais je veux obtenir le tableau suivant:

drop table parameter_test;
create table parameter_test
 (
  site_id       VARCHAR(30),
  parameter     VARCHAR(30),
  value         NUMBER,
  method        VARCHAR(30)
);

insert into parameter_test values ('site1', 'ANC', 10.1,'Gran 1952');
insert into parameter_test values('site2', 'ANC', 21.8, 'Gran 1952');
insert into parameter_test values('site1', 'NO3', 2.1, 'EPA 1983');
insert into parameter_test values('site2', 'NO3', 7.2, 'EPA 1983');

Comment puis-je obtenir les méthodes spécifiques à attacher au paramètre spécifique comme une nouvelle colonne? J'ai l'impression que cela pourrait être une sorte de déclaration groupe par rapport, mais je ne sais pas par où commencer.

Merci!

sql database group-by oracle pivot

1 commentaires

Pour commencer, je suppose que lorsque vous dites "numéro_paramètre", vous voulez dire "valeur" (il n'y a pas de colonne nommée numéro_paramètre dans votre table. Deuxièmement, la paire (paramètre, méthode) est-elle la même sur tous les sites? "Gran 1952" est-il toujours la méthode utilisée avec ANC? Si tel est le cas, vous êtes peut-être pour un peu de normalisation. Sinon, comment voudriez-vous le gérer? Obtenir des sommes par paire?

3 Réponses :

1
votes

Cela devrait le faire avec une jointure externe complète.

Je ne pense pas que ce soit possible avec un pivot s'il va y avoir plusieurs méthodes pour le même paramètre.

drop table parameter_pivot_test;
create table parameter_pivot_test
AS
(select coalesce(anc_site, no3_site) as site_id, anc, no3, anc_method, mo3_method from
(select site_id as anc_site, value as anc, method as anc_method from parameter_test where parameter = 'ANC') anc
FULL OUTER JOIN
(select site_id as no3_site, value as no3, method as no3_method from parameter_test where parameter = 'NO3') no3
ON anc.anc_site = no3.no3_site
)
;

2 commentaires

Cela fonctionne, mais est un peu lent (prend plus de 15 secondes pour ces 2 variables (ANC. NO3). J'ai environ 12 autres variables qui devraient être ajoutées.

Pas de problème, après avoir vu la réponse de GMB, je recommanderais que

3
votes

J'utiliserais simplement l'agrégation conditionnelle:

select
    site_id,
    max(case when parameter = 'ANC' then value end) anc,
    max(case when parameter = 'NO3' then value end) no3,
    max(case when parameter = 'ANC' then method end) anc_method,
    max(case when parameter = 'NO3' then method end) no3_method
from parameter_pivot
group by side_id

Cela fait pivoter les données sur une liste fixe de colonnes. Si vous voulez un pivot dynamique, alors vous avez besoin de SQL dynamique, qui est une autre bête.

4 commentaires

Les deux premiers agrégats doivent être SUM, pas MAX

@downernn: peut-être. C'est difficile à dire avec certitude. OP n'a montré aucun paramètre / valeur en double dans leurs exemples de données et n'a pas précisé comment gérer ce cas.

Correct, mais OP a utilisé sum (parameter_number) pour ... dans sa définition de pivot. Par cela, je suppose (et je pense que vous aussi), qu'ils signifiaient somme (valeur) pour ... . Il n'y a pas de colonne nommée "numéro_paramètre" (leur code génère une erreur) et les nombres affichés dans l'exemple sont les mêmes que dans la colonne "valeur". Je suis donc à peu près sûr qu'ils voulaient faire une somme sur la colonne «valeur».

Dans cet exemple de données, il n'y a pas de doublon de paramètre / valeur. Et dans l'ensemble de données complet, je vais regrouper par site_id, date et une autre colonne pour générer un ensemble unique de variables qui n'auront qu'une valeur par combinaison unique. Donc, somme ou max fonctionnera dans ce cas.

0
votes

Utilisez un PIVOT et agréger sur les colonnes value et method :

SITE_ID |  ANC | NOC | ANC_METHOD | NO3_METHOD
:------ | ---: | --: | :--------- | :---------
site2   | 21.8 | 7.2 | Gran 1952  | EPA 1983  
site1   | 10.1 | 2.1 | Gran 1952  | EPA 1983

Lequel pour données de test:

SELECT * FROM parameter_pivot_test;

Donne la sortie:

create table parameter_test
 (
  site_id       VARCHAR(30),
  parameter     VARCHAR(30),
  value         NUMBER,
  method        VARCHAR(30)
);

insert into parameter_test ( site_id, parameter, value, method )
SELECT 'site1', 'ANC', 10.1,'Gran 1952' FROM DUAL UNION ALL
SELECT 'site2', 'ANC', 21.8, 'Gran 1952' FROM DUAL UNION ALL
SELECT 'site1', 'NO3', 2.1, 'EPA 1983' FROM DUAL UNION ALL
SELECT 'site2', 'NO3', 7.2, 'EPA 1983' FROM DUAL;

CREATE TABLE parameter_pivot_test AS
SELECT site_id,
       anc_value AS anc,
       no3_value AS noc,
       anc_method,
       no3_method
FROM   parameter_test
PIVOT (
  SUM( value ) AS value,
  MAX( method ) AS method
  FOR parameter IN (
   'ANC' AS ANC,
   'NO3' AS NO3
  )
);

db fiddle ici em>

1 commentaires

Impossible de faire fonctionner cela sur mon ensemble de données complet, mais j'imagine que cela a quelque chose à voir avec plusieurs paramètres (10 autres plus ANC et NO3). J'y reviendrai peut-être quand j'aurai plus de temps.