How could I improve this SQL query with sub querys?

Question

How could I improve this SQL query with sub querys?

Navigation

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

0

There are many sub selects, how could I improve this query in MySQL and make a sum of max of 'total_points_period'?

Take the total of points for a period of the "user_bigdata" table and the total points of the "olympic_players_niveis" table according to the period.

The user_bigdata table is a list where you have all the points per user of the system, but I need to pick up and add all the maximum points of the same action_id the olympic_player_niveis score is the score of a given competition per period, which is related to the active competition on the olympic table, which contains the start and end times of this competition. I would like to relate both without needing a subquery with another subquery.

The current query:

SELECT 'tab'.'user_id', 'tab'.'total_points', 
                    (SELECT max(CASE WHEN ub.action_type = 'points' and ub.value <> 'giveup' and  ub.created between start_date and end_date THEN CAST(ub.value as UNSIGNED) ELSE 0 END ) AS pontos_obtidos
                        FROM user_bigdata ub
                        WHERE ub.usr_id = user_id 
                        AND (ub.action_type in ('points') and ub.type_utilization=2)
                        group by ub.action_id, ub.question_id) as total_points_period
                     FROM (
            SELECT 
                SUM(opn.points) AS 'total_points',
                ol.start_date AS start_date,
                ol.end_date AS end_date,
                u.ID as user_id,
                u.avatar_json

            FROM
                'olympic_players_niveis' AS 'opn'
                    INNER JOIN
                olympics ol ON (ol.active = 1 AND ol.ID = opn.olympic_id AND ol.ID = 1 )
                   INNER JOIN users u ON('opn'.'user_id'=u.ID)
            GROUP BY 'opn'.'user_id'
            ORDER BY SUM(opn.points) DESC , 'opn'.'created' DESC , FIELD(opn.tip_resolved, 1, NULL) DESC
            ) AS 'tab' ORDER BY 'total_points' DESC, 'total_points_period' DESC

Tables:

CREATE TABLE 'olympic_players_niveis' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'user_id' int(11) NOT NULL,
  'nivel_id' int(4) NOT NULL,
  'points' int(6) NOT NULL,
  'olympic_id' int(4) DEFAULT NULL,
  'created' timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  'tip_resolved' int(1) DEFAULT NULL,
  'updated' timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ('ID'),
  KEY 'fk_olympic_players_Olympics1_idx' ('olympic_id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE 'olympics' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'active' int(11) NOT NULL,
  'title' text CHARACTER SET utf8 NOT NULL,
  'badge_id' int(11) NOT NULL,
  'person_1' int(3) DEFAULT NULL,
  'person_2' int(3) DEFAULT NULL,
  'initial_talk_id' int(3) DEFAULT NULL,
  'end_talk_id' int(3) DEFAULT NULL,
  'logo' varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  'regulation' text COLLATE utf8_unicode_ci,
  'start_date' datetime DEFAULT NULL,
  'end_date' datetime DEFAULT NULL,
  'free_users_access' text COLLATE utf8_unicode_ci,
  PRIMARY KEY ('ID')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE 'user_bigdata' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'usr_id' int(4) NOT NULL,
  'action_type' varchar(50) NOT NULL,
  'action_id' int(4) NOT NULL,
  'question_id' int(4) NOT NULL,
  'data_type' varchar(30) NOT NULL,
  'value' varchar(100) NOT NULL,
  'created' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'start' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  'end' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  'seconds' int(5) NOT NULL,
  'answer' varchar(5) DEFAULT NULL,
  'answer_correct' int(2) DEFAULT NULL,
  'type_utilization' int(2) DEFAULT NULL,
  PRIMARY KEY ('ID'),
  KEY 'indice1' ('value','data_type','action_id','usr_id'),
  KEY 'type_value' ('data_type','value','usr_id'),
  KEY 'indiceusuario' ('usr_id'),
  KEY 'actiontimeid' ('action_type','action_id','question_id'),
  KEY 'action_search' ('usr_id','action_type','data_type','action_id'),
  KEY 'index_busca_atividade' ('usr_id','action_type','question_id','created','value','action_id'),
  KEY 'index_recentes' ('usr_id','action_id','action_type'),
  KEY 'index_data' ('created')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE 'users' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'username' varchar(200) CHARACTER SET utf8 NOT NULL,
  'role' varchar(50) CHARACTER SET utf8 NOT NULL,
  'password' varchar(500) CHARACTER SET utf8 NOT NULL,
  'status' int(11) NOT NULL,
  PRIMARY KEY ('ID'),
  KEY 'idx_role' ('ID','role')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The data would be basically these:

INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 1, 'points', 1, 0, 200, 2);
INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 1, 'points', 1, 0, 100, 2);
INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 1, 'points_tips', 1, 0, 50, 2);

INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 2, 'points', 1, 0, 100, 2);
INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 2, 'points', 1, 0, 80, 2);
INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 2, 'points_tips', 1, 0, 10, 2);

INSERT INTO 'user_bigdata' ('ID', 'usr_id', 'action_type', 'action_id', 'question_id', 'value', 'type_utilization') VALUES ('', 3, 'points', 1, 0, 'giveup', 2);

INSERT INTO  'olympics' ('ID', 'active', 'title', 'start_date', 'end_date') VALUES (1, 1, "Jogo", 0, DATE_ADD(NOW(), INTERVAL -1 DAY), DATE_ADD(NOW(), INTERVAL +10 DAY));

INSERT INTO  'olympic_players_niveis' ('ID', 'user_id', 'nivel_id', 'points', 'olympic_id', 'created', 'tip_resolved')
VALUES ('', 1, 1, 30, 1, now(), null); 

INSERT INTO  'olympic_players_niveis' ('ID', 'user_id', 'nivel_id', 'points', 'olympic_id', 'created', 'tip_resolved')
VALUES ('', 2, 1, 130, 1, now(), null); 

INSERT INTO 'users' ('ID', 'username', 'role', 'password') VALUES (1, 'ononnoonon1', 'nonono', 'onnonono');
INSERT INTO 'users' ('ID', 'username', 'role', 'password') VALUES (2, 'ononnoonon2', 'nonono', 'onnonono');
INSERT INTO 'users' ('ID', 'username', 'role', 'password') VALUES (3, 'ononnoonon2', 'nonono', 'onnonono');

SQL Fiddle

mysql sql query

asked by anonymous 24.07.2018 / 19:33

2 answers

0

@van, I saw that you changed your table by adding a new table and changing the other table. But since your INSERT data is not up to date, I was not able to test with the new structure.

But based on the structure of this example you passed, I created this SQL. I believe this meets your need and I also believe it is easy to understand and adapt.

Follow query :

SELECT 
SUM(opn.points) as points,
new.total_pontos,
ol.start_date AS start_d,
ol.end_date AS end_d,
opn.user_id
FROM olympic_players_niveis AS opn
INNER JOIN olympics ol ON (ol.active = 1 AND ol.ID = opn.olympic_id)
INNER JOIN (
    SELECT ub.usr_id, SUM(ub.value) as total_pontos
    FROM user_bigdata ub
    INNER JOIN olympic_players_niveis o ON (o.ID = ub.usr_id)
    INNER JOIN olympics ol ON (ol.id = o.olympic_id)
    WHERE ub.action_type IN ('points') AND ub.created BETWEEN ol.start_date AND ol.end_date AND ol.active = 1
    GROUP BY ub.usr_id
) new ON (new.usr_id = opn.user_id)
GROUP BY opn.user_id

If you want to return only a specific user, just put

WHERE opn.user_id = ?

Before GROUP BY , where ? you replace with id of user.

26.07.2018 / 00:59

Problem trying to login with Facebook Reading file shared by other applications on Android

score 0 · Accepted Answer

How did I solve the problems:

select user_id_int, total_points , sum(pontos_obtidos_period) as pontos_periodo, u.avatar_json, ano from (

                    SELECT user_id_int, total_points , ub.action_id, ub.question_id, start_date, end_date, ano,
                    max(CAST(ub.value as UNSIGNED))  AS pontos_obtidos_period  

                    FROM (
                            SELECT 
                                SUM(opn.points) AS 'total_points',
                                ol.start_date AS start_date,
                                ol.end_date AS end_date,
                                opn.user_id as user_id_int,
                                (case when sc.scc_year is null then opi.opi_school_year else sc.scc_year end) as ano

                            FROM
                                'olympic_players_niveis' AS 'opn'
                                    INNER JOIN
                                olympics ol ON ( ol.ID = opn.olympic_id AND ol.ID = 7 )

                                 left JOIN school_class_members as scm ON(scm.usr_id = opn.user_id) 
                                 left JOIN Schools as s ON(s.ID = scm.sch_id) 
                                 left JOIN school_classes as sc ON(sc.sch_id = s.ID and scm.cls_id = sc.scc_id and sc.scc_status = 1) 
                                 left join Planos as p on p.ID = s.plan and (p.data_exp > CURDATE())
                                 left join olympic_players_initial opi on opi.usr_id = opn.user_id


                            GROUP BY 'opn'.'user_id'
                            ORDER BY 'opn'.'created' DESC , FIELD(opn.tip_resolved, 1, NULL) DESC
                    ) AS 'tab' 

                    left join user_bigdata ub 
                    on (ub.usr_id = user_id_int 
                    and ub.action_type = 'points' 
                    and ub.value <> 'giveup' 
                     and ub.created between start_date and end_date)

                    group by user_id_int, ub.action_id
) as tab4 
inner join Users u on u.ID = user_id_int

group by user_id_int
order by total_points desc, pontos_periodo desc;