Retrieve total of items from combinations

Question

Retrieve total of items from combinations

Navigation

#1 by (1 votes)
#2 by (0 votes)

4

I have the following table in a PostgreSQL database:

CREATE TABLE combinaciones
(
   id    serial NOT NULL,
   valor character varying NOT NULL,
   CONSTRAINT id_pk PRIMARY KEY (id)
) WITH (OIDS=FALSE);

ALTER TABLE combinaciones OWNER TO postgres;

To which I have inserted the following values:

INSERT INTO combinaciones (valor)
VALUES
  ('A.1;B.1;F'),
  ('A.2.1;B.1;F'),
  ('A.2.1;F'),
  ('A.3;B.1;F'),
  ('A.3;B.2;F'),
  ('A.3;C;D.1;F'),
  ('A.3;C;F'),
  ('A.3;D.1;F'),
  ('A.3;F'),
  ('B.1;B.2;F');

I need to recover the total of items loaded in all combinations, something like this:

A.1   =  1 veces
A.2.1 =  2 veces
A.3   =  6 veces
B.1   =  4 veces
B.2   =  2 veces
C     =  2 veces
D.1   =  2 veces
F     = 10 veces

However, when executing the following query:

SELECT
  (
    CASE
      WHEN combinacion LIKE '%A.1%'   THEN 'A.1'
      WHEN combinacion LIKE '%A.2.1%' THEN 'A.2.1'
      WHEN combinacion LIKE '%A.2.2%' THEN 'A.2.2'
      WHEN combinacion LIKE '%A.3%'   THEN 'A.3'
      WHEN combinacion LIKE '%A.4%'   THEN 'A.4'
      WHEN combinacion LIKE '%B.1%'   THEN 'B.1'
      WHEN combinacion LIKE '%B.2%'   THEN 'B.21'
      WHEN combinacion LIKE '%C%'     THEN 'C'
      WHEN combinacion LIKE '%D.1%'   THEN 'D.1'
      WHEN combinacion LIKE '%D.2%'   THEN 'D.2'
      WHEN combinacion LIKE '%D.3%'   THEN 'D.3'
      WHEN combinacion LIKE '%E.1%'   THEN 'E.1'
      WHEN combinacion LIKE '%E.2%'   THEN 'E.2'
      WHEN combinacion LIKE '%E.3%'   THEN 'E.3'
      WHEN combinacion LIKE '%F%'     THEN 'F'
      WHEN combinacion LIKE '%G.1%'   THEN 'G.1'
      WHEN combinacion LIKE '%G.2%'   THEN 'G.2'
    END
  ) AS item,
  SUM(total) AS total
FROM (
  SELECT
    valor        AS combinacion,
    COUNT(valor) AS total
  FROM combinaciones
  GROUP BY combinacion
  ORDER BY 2,1
  ) AS resultado
GROUP BY item
ORDER BY item;

Return the following:

A.1   = 1 veces
A.2.1 = 2 veces
A.3   = 6 veces
B.1   = 1 veces

Where not only the quantities of some items do not match, but others are also overlooked and do not end up appearing in the final result.

Any suggestions on how to correct the SQL? Thanks from now.

Annex the test of the exercise here: consultation .

sql postgresql

asked by мαяςєlσ 13.06.2018 в 02:04

source

2 answers

0

Hello, the problem is because in the internal query, which searches the strings with the like, it does not work as it may seem at first, since you are assuming that it will do all the evaluations of the case for each row, which does not it is right. If you notice, that query alone, returns you 10 rows even though you have separate conditions for each case, this is so because the case to find a condition that meets the expected ends its execution. To see it with your case we have the value:

fila 1 -> valor ('A.1;B.1;F'); entra en la condición: WHEN valor LIKE '%A.1%'   THEN 'A.1'

At this point and the first row was evaluated, and a condition was found in the case that meets it, so it is passed to the next row and for each the same thing happens. We go with the second row:

fila 2 -> ('A.2.1;B.1;F'); entra en la condición: WHEN valor LIKE '%A.2.1%' THEN 'A.2.1'

We already have two rows processed and we have lost the "B.1; F" values of both rows.

At the end the result of the internal query is:

A.1
A.2.1
A.2.1
A.3
A.3
A.3
A.3
A.3
A.3
B.1

You will notice that it is the result of the first condition found in the case for each row .

You have to see the processing of the case by row, the SQL language works by rows. Also try to work the query piece by piece, first the internal query and so on to see these errors at once.

What you must do first is to separate the values in rows, notice that you have a ";" for each value that can help you to separate; once you have that in rows you can apply the same case you have and this should give you the expected result.

This:

('A.1;B.1;F');

It should look like:

A.1
B.1
F

Greetings!

answered by 13.06.2018 в 04:18

Android Studio preview error empty the records of a mysql table with event_scheduler 24h

score 1 · Accepted Answer

The query that you are asking does not work for you, because you can not really count beyond the number of rows of combinaciones and what actually happens is that each row can have more than one item. Knowing this, what we can do is "exploit" each row by unnest() and string_to_array() separating the string by ; and generating new rows, then just I would have to count the appearances of each item:

SELECT s.token, count(1)
    FROM   combinaciones t, unnest(string_to_array(t.valor, ';')) s(token)
    group by s.token;

Exit:

+---+-------+-------+
| # | token | count |
+---+-------+-------+
| 1 | C     | 2     |
+---+-------+-------+
| 2 | A.3   | 6     |
+---+-------+-------+
| 3 | B.2   | 2     |
+---+-------+-------+
| 4 | D.1   | 2     |
+---+-------+-------+
| 5 | F     | 10    |
+---+-------+-------+
| 6 | B.1   | 4     |
+---+-------+-------+
| 7 | A.1   | 1     |
+---+-------+-------+
| 8 | A.2.1 | 2     |
+---+-------+-------+

Postgres < 8.4

We have regexp_split_to_table() to expand each row according to the items it contains

select  s.token, count(1)
    from (select regexp_split_to_table(valor, ';') as token
            from combinaciones
       ) s
    group by s.token;