mysql: fields and count (*) of union

Question

mysql: fields and count (*) of union

Navigation

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

3

Following the query I made a few days ago ( mysql pivoting table result of fields json ) I have found another problem. It turns out that I have to obtain several fields of union and divide it by the total number of elements that the union has.

I currently have:

select
    concat("assembled") as field, concat("1") as veces,
    count(if(json_extract(filters, '$.assembled')= true,json_extract(filters, '$.assembled'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("windows") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.osWindows')= true,json_extract(filters, '$.osWindows'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("linux") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.osLinux')= true,json_extract(filters, '$.osLinux'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("mac") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.osMac')= true,json_extract(filters, '$.osMac'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("sdcard") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.sdCard')= true,json_extract(filters, '$.sdCard'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("usb") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.usb')= true,json_extract(filters, '$.usb'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("ethernet") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.ethernet')= true,json_extract(filters, '$.ethernet'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("wifi") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.wifi')= true,json_extract(filters, '$.wifi'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("bluetooth") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.bluetooth')= true,json_extract(filters, '$.bluetooth'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Display") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.integratedDisplay')= true,json_extract(filters, '$.integratedDisplay'),NULL))as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("touchable") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.touchableDisplay')= true,json_extract(filters, '$.touchableDisplay'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Camera") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.integratedCamera')= true,json_extract(filters, '$.integratedCamera'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Scanner") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.builtinScanner')= true,json_extract(filters, '$.builtinScanner'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("mobileApp") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.mobileApp')= true,json_extract(filters, '$.mobileApp'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Computer") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.integratedComputer')= true,json_extract(filters, '$.integratedComputer'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("closedFrame") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.closedFrame')= true,json_extract(filters, '$.closedFrame'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)
union
select
    concat("securityLock") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.securityLock')= true,json_extract(filters, '$.securityLock'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("emergencyStop") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.emergencyStop')= true,json_extract(filters, '$.emergencyStop'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)
union
select
    concat("innerLight") as field,concat("1") as veces,
    count(if(json_extract(filters, '$.innerLight')= true,json_extract(filters, '$.innerLight'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)

This generates me a table with the following data:

+---------------+-------+--------+
| field         | veces | cuenta |
+---------------+-------+--------+
| assembled     | 1     |      1 |
| assembled     | 1     |      9 |
| assembled     | 1     |      0 |
| assembled     | 1     |      8 |
| assembled     | 1     |      4 |
| assembled     | 1     |      6 |
| assembled     | 1     |      5 |
| assembled     | 1     |      7 |
| assembled     | 1     |     17 |
| assembled     | 1     |     11 |
| assembled     | 1     |      3 |
| assembled     | 1     |     14 |
| assembled     | 1     |     13 |
| assembled     | 1     |     15 |
| assembled     | 1     |      2 |
| windows       | 1     |      0 |
| windows       | 1     |      9 |
| windows       | 1     |     25 |
| windows       | 1     |      2 |
| windows       | 1     |      6 |
| windows       | 1     |      5 |
| windows       | 1     |     11 |
| windows       | 1     |      1 |
| windows       | 1     |      8 |
| windows       | 1     |      4 |
| linux         | 1     |      0 |
| linux         | 1     |      4 |
| linux         | 1     |     11 |
| linux         | 1     |      1 |
| linux         | 1     |      9 |
| linux         | 1     |      8 |
| mac           | 1     |      0 |
| mac           | 1     |      9 |
| mac           | 1     |     11 |
| mac           | 1     |      1 |
| mac           | 1     |      8 |
| sdcard        | 1     |      0 |
| sdcard        | 1     |      9 |
| sdcard        | 1     |     11 |
| sdcard        | 1     |      1 |
| sdcard        | 1     |      8 |
| usb           | 1     |      0 |
| usb           | 1     |      8 |
| usb           | 1     |      9 |
| usb           | 1     |     20 |
| usb           | 1     |      5 |
| usb           | 1     |      7 |
| usb           | 1     |      4 |
| usb           | 1     |      1 |
| usb           | 1     |      2 |
| ethernet      | 1     |      0 |
| ethernet      | 1     |      1 |
| ethernet      | 1     |      4 |
| ethernet      | 1     |      2 |
| ethernet      | 1     |      5 |
| wifi          | 1     |      0 |
| wifi          | 1     |      5 |
| wifi          | 1     |      1 |
| wifi          | 1     |      2 |
| bluetooth     | 1     |      0 |
| bluetooth     | 1     |      1 |
| Display       | 1     |      0 |
| Display       | 1     |      8 |
| Display       | 1     |      4 |
| Display       | 1     |      7 |
| Display       | 1     |      9 |
| Display       | 1     |      3 |
| Display       | 1     |      1 |
| Display       | 1     |     13 |
| Display       | 1     |     15 |
| Display       | 1     |      5 |
| touchable     | 1     |      0 |
| touchable     | 1     |      2 |
| touchable     | 1     |      1 |
| Camera        | 1     |      0 |
| Scanner       | 1     |      0 |
| mobileApp     | 1     |      0 |
| Computer      | 1     |      0 |
| closedFrame   | 1     |      0 |
| closedFrame   | 1     |      1 |
| securityLock  | 1     |      1 |
| securityLock  | 1     |      0 |
| emergencyStop | 1     |      0 |
| emergencyStop | 1     |      1 |
| innerLight    | 1     |      0 |
| innerLight    | 1     |      1 |
+---------------+-------+--------+
86 rows in set (0,50 sec)

My goal now is to get this same table, but with the field "times" divided by 86. That is, the total of elements that result from the query ... of the union !!!

initially I have included all the union query in a select such that:

select field, veces, cuenta 
from (
    ... // aqui va todo el select/union

) as T1;

With this query, I have the same previous table of results again.But when trying to divide by the total of elements, with a

count (*):

select field, times / count (*), count from (       ... // here goes all the select / union

) as T1;

the result is:

+---------------------+-------+
| T1.field / count(*) | veces |
+---------------------+-------+
|                   0 | 1     |
+---------------------+-------+
1 row in set, 1 warning (0,51 sec)

Does anyone come up with any way to get the total union rows, and divide a field by that total?

Thanks in advance, and happy Christmas !!!

mysql sql

asked by Jakala 29.12.2016 в 11:40

source

3 answers

Create my own payload in python Where are the private attributes of a super class stored?

score 1 · Answer 1

Your example is too detailed, and that complicates your follow-up a bit. I'll give you a simpler example of what you want to achieve, to see if it's worth it:

declare @tabla as table (
    veces int,
    nombre char(50)
)

insert into @tabla values (1, 'Hola')
insert into @tabla values (1, 'Hello')
insert into @tabla values (1, 'Adios')
insert into @tabla values (1, 'Bye')

select veces, cuenta, cast(veces as decimal) / cuenta as Ratio from (
    select count(*) as Cuenta from @tabla
) as t1  inner join @tabla
on 1 = 1

It is an example that divides the column times among the total rows. I have put it in a variable table to avoid doing twice the large query (one to calculate the total and another to read the individual records). But if you wanted to do it by executing the big query twice, it would be like this:

select field, cast(veces as decimal) / cuenta, cuenta from (
    select count(*) as cuenta from (
        -- Aquí iría la consulta grande
    ) as t
) as t1 inner join (
    -- Aquí iría la consulta grande
) as t2
on 1 = 1

On the other hand, and if you allow me to comment on what you have NOT asked, I would not make the initial consultation with so many UNIONS that seriously damage the performance of it. Have you considered doing it simply with a GROUP BY and a CASE? It is usually more interesting to make a single query than so many queries with UNION.

I also do not understand why you use CONCAT to put a value 1 in the "times" field, if you then use it as a decimal to divide it. Surely there is a reason, but it would be good to explain it.

score 0 · Answer 2

0

You can directly use the avg :

SELECT avg(veces) FROM(
     //Aquí tu select union
) AS media

And it will return the average of the values in the column veces .

answered by 29.12.2016 в 11:53

score 0 · Answer 3

As a quick summary, I have to do everything in a query by technical decision (we use a manager that only supports queries, and we can not schedule anything in it). Also, it does not allow me to use things like set @ var = 0. The main problem is that I can not do a count () or avg () because I do not have to group a unique id in the result. : (

Anyway, it is already solved. I will not focus on optimization (because it is clear that it is not optimized). I put it here in case someone serves ... if we were on Halloween, I would give sooo scary ...;)

select  field, if(cuenta>0, 100, 0) /
(select count(*) from(
  select
      concat("assembled") as field,
      count(if(json_extract(filters, '$.assembled')= true,json_extract(filters, '$.assembled'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("windows") as field,
      count(if(json_extract(filters, '$.osWindows')= true,json_extract(filters, '$.osWindows'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("linux") as field,
      count(if(json_extract(filters, '$.osLinux')= true,json_extract(filters, '$.osLinux'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("mac") as field,
      count(if(json_extract(filters, '$.osMac')= true,json_extract(filters, '$.osMac'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("sdcard") as field,
      count(if(json_extract(filters, '$.sdCard')= true,json_extract(filters, '$.sdCard'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("usb") as field,
      count(if(json_extract(filters, '$.usb')= true,json_extract(filters, '$.usb'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("ethernet") as field,
      count(if(json_extract(filters, '$.ethernet')= true,json_extract(filters, '$.ethernet'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("wifi") as field,
      count(if(json_extract(filters, '$.wifi')= true,json_extract(filters, '$.wifi'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("bluetooth") as field,
      count(if(json_extract(filters, '$.bluetooth')= true,json_extract(filters, '$.bluetooth'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("Display") as field,
      count(if(json_extract(filters, '$.integratedDisplay')= true,json_extract(filters, '$.integratedDisplay'),NULL))as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("touchable") as field,
      count(if(json_extract(filters, '$.touchableDisplay')= true,json_extract(filters, '$.touchableDisplay'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("Camera") as field,
      count(if(json_extract(filters, '$.integratedCamera')= true,json_extract(filters, '$.integratedCamera'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("Scanner") as field,
      count(if(json_extract(filters, '$.builtinScanner')= true,json_extract(filters, '$.builtinScanner'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("mobileApp") as field,
      count(if(json_extract(filters, '$.mobileApp')= true,json_extract(filters, '$.mobileApp'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("Computer") as field,
      count(if(json_extract(filters, '$.integratedComputer')= true,json_extract(filters, '$.integratedComputer'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("closedFrame") as field,
      count(if(json_extract(filters, '$.closedFrame')= true,json_extract(filters, '$.closedFrame'),NULL)) as cuenta
      from bigdata_filtered group by concat(ip, filtered_date)
  union
  select
      concat("securityLock") as field,
      count(if(json_extract(filters, '$.securityLock')= true,json_extract(filters, '$.securityLock'),NULL)) as cuenta
      from bigdata_filtered  group by concat(ip, filtered_date)
  union
  select
      concat("emergencyStop") as field,
      count(if(json_extract(filters, '$.emergencyStop')= true,json_extract(filters, '$.emergencyStop'),NULL)) as cuenta
      from bigdata_filtered group by concat(ip, filtered_date)
  union
  select
      concat("innerLight") as field,
      count(if(json_extract(filters, '$.innerLight')= true,json_extract(filters, '$.innerLight'),NULL)) as cuenta
      from bigdata_filtered group by concat(ip, filtered_date)
  )as C1

)

 as cuenta
from (
select
    concat("assembled") as field,
    count(if(json_extract(filters, '$.assembled')= true,json_extract(filters, '$.assembled'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("windows") as field,
    count(if(json_extract(filters, '$.osWindows')= true,json_extract(filters, '$.osWindows'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("linux") as field,
    count(if(json_extract(filters, '$.osLinux')= true,json_extract(filters, '$.osLinux'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("mac") as field,
    count(if(json_extract(filters, '$.osMac')= true,json_extract(filters, '$.osMac'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("sdcard") as field,
    count(if(json_extract(filters, '$.sdCard')= true,json_extract(filters, '$.sdCard'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("usb") as field,
    count(if(json_extract(filters, '$.usb')= true,json_extract(filters, '$.usb'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("ethernet") as field,
    count(if(json_extract(filters, '$.ethernet')= true,json_extract(filters, '$.ethernet'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("wifi") as field,
    count(if(json_extract(filters, '$.wifi')= true,json_extract(filters, '$.wifi'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("bluetooth") as field,
    count(if(json_extract(filters, '$.bluetooth')= true,json_extract(filters, '$.bluetooth'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Display") as field,
    count(if(json_extract(filters, '$.integratedDisplay')= true,json_extract(filters, '$.integratedDisplay'),NULL))as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("touchable") as field,
    count(if(json_extract(filters, '$.touchableDisplay')= true,json_extract(filters, '$.touchableDisplay'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Camera") as field,
    count(if(json_extract(filters, '$.integratedCamera')= true,json_extract(filters, '$.integratedCamera'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Scanner") as field,
    count(if(json_extract(filters, '$.builtinScanner')= true,json_extract(filters, '$.builtinScanner'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("mobileApp") as field,
    count(if(json_extract(filters, '$.mobileApp')= true,json_extract(filters, '$.mobileApp'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("Computer") as field,
    count(if(json_extract(filters, '$.integratedComputer')= true,json_extract(filters, '$.integratedComputer'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("closedFrame") as field,
    count(if(json_extract(filters, '$.closedFrame')= true,json_extract(filters, '$.closedFrame'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)
union
select
    concat("securityLock") as field,
    count(if(json_extract(filters, '$.securityLock')= true,json_extract(filters, '$.securityLock'),NULL)) as cuenta
    from bigdata_filtered  group by concat(ip, filtered_date)
union
select
    concat("emergencyStop") as field,
    count(if(json_extract(filters, '$.emergencyStop')= true,json_extract(filters, '$.emergencyStop'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)
union
select
    concat("innerLight") as field,
    count(if(json_extract(filters, '$.innerLight')= true,json_extract(filters, '$.innerLight'),NULL)) as cuenta
    from bigdata_filtered group by concat(ip, filtered_date)
) as T1
;