Archiver query

query.sql

SELECT
				
				CASE
					WHEN counter = 50001 THEN '-1'
					ELSE `idaction`
				END AS `idaction`
			, 
				CASE
					WHEN counter = 50001 THEN '-1'
					ELSE `name`
				END AS `name`
			
				, `url_prefix`, min(`28`) AS `28`, sum(`29`) AS `29`, sum(`2`) AS `2`, `1`, sum(`12`) AS `12`, sum(`30`) AS `30`, sum(`31`) AS `31`, min(`32`) AS `32`, max(`33`) AS `33`, `type`
			FROM ( 
			SELECT
				`idaction`, `name`,
				
			CASE
				WHEN `type` = 1 AND @counter1 = 50001 THEN 50001
				WHEN `type` = 1 THEN @counter1:=@counter1+1
				WHEN `type` = 2 AND @counter2 = 50001 THEN 50001
				WHEN `type` = 2 THEN @counter2:=@counter2+1
				WHEN `type` = 3 AND @counter3 = 50001 THEN 50001
				WHEN `type` = 3 THEN @counter3:=@counter3+1
				WHEN `type` = 4 AND @counter4 = 50001 THEN 50001
				WHEN `type` = 4 THEN @counter4:=@counter4+1
				WHEN `type` = 8 AND @counter8 = 50001 THEN 50001
				WHEN `type` = 8 THEN @counter8:=@counter8+1
				ELSE 0
			END
		 AS counter
				, `url_prefix`, `28`, `29`, `2`, `1`, `12`, `30`, `31`, `32`, `33`, `type`
			FROM
				( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4, ( SELECT @counter8:=0 ) initCounter8, 
				( /* trigger = CronArchive */

			SELECT
				log_action.name,
                log_action.type,
                log_action.idaction,
                log_action.url_prefix, count(distinct log_link_visit_action.idvisit) as `2`, count(distinct log_link_visit_action.idvisitor) as `1`, count(*) as `12`, sum(
                        case when custom_float is null
                            then 0
                            else custom_float
                        end
                ) / 1000 as `30`, sum(
                    case when custom_float is null
                        then 0
                        else 1
                    end
                ) as `31`, min(custom_float) / 1000 as `32`, max(custom_float) / 1000 as `33`,
                CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = 0
                    AND log_link_visit_action.custom_var_k5 = '_pk_scount')
                THEN 1 ELSE 0 END
                    AS `28`,
                SUM( CASE WHEN log_action_name_ref.type = 8
                      THEN 1 ELSE 0 END)
                    AS `29`
			FROM
				piwik_log_link_visit_action AS log_link_visit_action LEFT JOIN piwik_log_action AS log_action_name_ref ON log_link_visit_action.idaction_name_ref = log_action_name_ref.idaction LEFT JOIN piwik_log_action AS log_action ON log_link_visit_action.idaction_name = log_action.idaction
			WHERE
				log_link_visit_action.server_time >= '2017-01-01 23:00:00'
                AND log_link_visit_action.server_time <= '2017-01-02 22:59:59'
                AND log_link_visit_action.idsite = '3'
                AND log_link_visit_action.idaction_name IS NOT NULL AND log_link_visit_action.idaction_event_category IS NULL
			GROUP BY
				log_action.idaction
			ORDER BY
				`12` DESC, name ASC ) actualQuery
		 ) AS withCounter
			GROUP BY counter, `type`