D'agrégation SQL par la valeur de la colonne unique

Question 1

Supposons que j'ai un tableau comme ceci:

Id	url
1	11.22.33
2	11.22.33
3	domain.com
4	domain2.com
5	domain.com
6	10.100.15
7	domain3.com
8	172.100.15
9	172.100.15

Dans ce tableau, certaines adresses IP sont associées à certains domaines, et nous pouvons considérer qu'il s'agit URL unique. (Par exemple, domain2.com, 10.100.15, 11.22.33 - c'est domain2.com; 172.100.15, domain3.com - domain3.com, etc). Ma tâche est que je dois calculer les lignes avec des domaines différents et faire un tableau comme ceci:

url	le comte
domain2	4
domaine	2
domain3	2

Je pouvais les compter avec plusieurs requêtes, telles que:

SELECT COUNT(*) 
FROM table1
WHERE (table1.url = “10.100.15”
    OR table1.url = “11.22.33” OR table1.url = “domain2.com”)

et faire de ce tableau à la main, mais c'est pas cool.

Comment pourrais-je le faire en une seule requête?

PS: comme mentionné dans le commentaire ci-dessous, d'habitude agrégation pourrait être fait avec une simple requête:

SELECT url, count(*) 
FROM table1
WHERE GROUP BY url

Mais il me donne seulement la table comme ceci:

URL	le comte
domain2	1
domaine	2
11.22.33	2
10.100.15	1

Ou ai-je raté quelque chose?

Question 2

Vous pouvez les regrouper par un CASE expression:

SELECT CASE 
         WHEN URL IN ('10.100.15', '11.22.33', 'domain2.com') THEN 'domain2.com'
         WHEN URL IN ('172.100.15', 'domain3.com') THEN 'domain3.com'
         ELSE URL
       END domain,
       COUNT(*) count
FROM tablename
GROUP BY domain;

Voir la démo.

Question 3

D'une façon plus élégante est d'avoir l'association stockées dans la base de données pour réaliser que:

WITH
-- your input ...
indata(Id,url) AS (
          SELECT 1,'11.22.33'
UNION ALL SELECT 2,'11.22.33'
UNION ALL SELECT 3,'domain.com'
UNION ALL SELECT 4,'domain2.com'
UNION ALL SELECT 5,'domain.com'
UNION ALL SELECT 6,'10.100.15'
UNION ALL SELECT 7,'domain3.com'
UNION ALL SELECT 8,'172.100.15'
UNION ALL SELECT 9,'172.100.15'
)
,
-- need an association table - which "url" belongs to which domain
assoc(dom,url) AS (
            SELECT 'domain2.com','10.100.15'
  UNION ALL SELECT 'domain2.com','11.22.33'
  UNION ALL SELECT 'domain3.com','172.100.15'
)
SELECT
  CASE 
    WHEN a.dom IS NULL THEN i.url
    ELSE a.dom
  END AS domain
, COUNT(*) AS counter
FROM indata AS i
LEFT
JOIN assoc  AS a USING(url)
GROUP BY domain
;

-- out
-- out    domain    | counter
-- out -------------+---------
-- out  domain2.com |       4                                                                                                                                                                             
-- out  domain3.com |       3
-- out  domain.com  |       2
-- out (3 rows)

Question 4

Vous devez utiliser du groupe par fonction et écrire votre code comme ceci:

SÉLECTIONNEZ l'URL, count(*) from table1 où l'URL('domaine1','domain2') groupe par (URL)

il va travailler!

forpas · Answer 1 · 2021-11-14T13:52:59

Vous pouvez les regrouper par un CASE expression:

SELECT CASE 
         WHEN URL IN ('10.100.15', '11.22.33', 'domain2.com') THEN 'domain2.com'
         WHEN URL IN ('172.100.15', 'domain3.com') THEN 'domain3.com'
         ELSE URL
       END domain,
       COUNT(*) count
FROM tablename
GROUP BY domain;

Voir la démo.

marcothesane · Answer 2 · 2021-11-14T13:46:49

D'une façon plus élégante est d'avoir l'association stockées dans la base de données pour réaliser que:

WITH
-- your input ...
indata(Id,url) AS (
          SELECT 1,'11.22.33'
UNION ALL SELECT 2,'11.22.33'
UNION ALL SELECT 3,'domain.com'
UNION ALL SELECT 4,'domain2.com'
UNION ALL SELECT 5,'domain.com'
UNION ALL SELECT 6,'10.100.15'
UNION ALL SELECT 7,'domain3.com'
UNION ALL SELECT 8,'172.100.15'
UNION ALL SELECT 9,'172.100.15'
)
,
-- need an association table - which "url" belongs to which domain
assoc(dom,url) AS (
            SELECT 'domain2.com','10.100.15'
  UNION ALL SELECT 'domain2.com','11.22.33'
  UNION ALL SELECT 'domain3.com','172.100.15'
)
SELECT
  CASE 
    WHEN a.dom IS NULL THEN i.url
    ELSE a.dom
  END AS domain
, COUNT(*) AS counter
FROM indata AS i
LEFT
JOIN assoc  AS a USING(url)
GROUP BY domain
;

-- out
-- out    domain    | counter
-- out -------------+---------
-- out  domain2.com |       4                                                                                                                                                                             
-- out  domain3.com |       3
-- out  domain.com  |       2
-- out (3 rows)

Généralement parlant, c'est une approche astucieuse, bien qu'un peu redondant dans mon cas. De toute façon, je vous remercie! Il pourrait être à portée de main.

vahidrobati · Answer 3 · 2021-11-14T16:28:37

Vous devez utiliser du groupe par fonction et écrire votre code comme ceci:

SÉLECTIONNEZ l'URL, count(*) from table1 où l'URL('domaine1','domain2') groupe par (URL)

il va travailler!

Non, il n'est pas. Cela me donne de comptage par chaque URL. J'ai de combiner certaines lignes que d'attribuer à UN seul domaine.

D'agrégation SQL par la valeur de la colonne unique

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie