API
7 months ago
Access
7 months ago
Application
1 year ago
Archive
7 months ago
ArchiveProcessor
7 months ago
Archiver
2 years ago
AssetManager
7 months ago
Auth
7 months ago
Category
7 months ago
Changes
7 months ago
CliMulti
1 year ago
Columns
7 months ago
Concurrency
7 months ago
Config
7 months ago
Container
7 months ago
CronArchive
7 months ago
DataAccess
7 months ago
DataFiles
2 years ago
DataTable
7 months ago
Db
7 months ago
DeviceDetector
1 year ago
Email
2 years ago
Exception
1 year ago
Http
1 year ago
Intl
7 months ago
Log
2 years ago
Mail
1 year ago
Measurable
7 months ago
Menu
7 months ago
Metrics
7 months ago
Notification
7 months ago
Period
7 months ago
Plugin
7 months ago
Policy
7 months ago
ProfessionalServices
1 year ago
Report
1 year ago
ReportRenderer
1 year ago
Request
7 months ago
Scheduler
7 months ago
Segment
7 months ago
Session
7 months ago
Settings
7 months ago
Tracker
7 months ago
Translation
1 year ago
Twig
1 year ago
UpdateCheck
7 months ago
Updater
7 months ago
Updates
7 months ago
Validators
1 year ago
View
7 months ago
ViewDataTable
7 months ago
Visualization
1 year ago
Widget
1 year ago
.htaccess
2 years ago
Access.php
7 months ago
Archive.php
7 months ago
ArchiveProcessor.php
7 months ago
AssetManager.php
1 year ago
Auth.php
7 months ago
AuthResult.php
7 months ago
BaseFactory.php
2 years ago
Cache.php
2 years ago
CacheId.php
1 year ago
CliMulti.php
7 months ago
Common.php
7 months ago
Config.php
1 year ago
Console.php
1 year ago
Context.php
2 years ago
Cookie.php
1 year ago
CronArchive.php
7 months ago
DI.php
1 year ago
DataArray.php
1 year ago
DataTable.php
7 months ago
Date.php
7 months ago
Db.php
1 year ago
DbHelper.php
7 months ago
Development.php
1 year ago
ErrorHandler.php
7 months ago
EventDispatcher.php
1 year ago
ExceptionHandler.php
7 months ago
FileIntegrity.php
1 year ago
Filechecks.php
1 year ago
Filesystem.php
1 year ago
FrontController.php
7 months ago
Http.php
7 months ago
IP.php
1 year ago
Log.php
7 months ago
LogDeleter.php
1 year ago
Mail.php
1 year ago
Metrics.php
7 months ago
NoAccessException.php
2 years ago
Nonce.php
7 months ago
Notification.php
7 months ago
NumberFormatter.php
1 year ago
Option.php
1 year ago
Period.php
1 year ago
Piwik.php
7 months ago
Plugin.php
1 year ago
Process.php
1 year ago
Profiler.php
7 months ago
ProxyHeaders.php
2 years ago
ProxyHttp.php
1 year ago
QuickForm2.php
1 year ago
RankingQuery.php
7 months ago
ReportRenderer.php
1 year ago
Request.php
1 year ago
Segment.php
7 months ago
Sequence.php
7 months ago
Session.php
1 year ago
SettingsPiwik.php
1 year ago
SettingsServer.php
1 year ago
Singleton.php
2 years ago
Site.php
7 months ago
SiteContentDetector.php
1 year ago
SupportedBrowser.php
2 years ago
TCPDF.php
1 year ago
Theme.php
1 year ago
Timer.php
2 years ago
Tracker.php
1 year ago
Twig.php
7 months ago
Unzip.php
1 year ago
UpdateCheck.php
1 year ago
Updater.php
1 year ago
UpdaterErrorException.php
2 years ago
Updates.php
1 year ago
Url.php
7 months ago
UrlHelper.php
1 year ago
Version.php
7 months ago
View.php
7 months ago
bootstrap.php
1 year ago
dispatch.php
2 years ago
testMinimumPhpVersion.php
7 months ago
RankingQuery.php
381 lines
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Matomo - free/libre analytics platform |
| 5 | * |
| 6 | * @link https://matomo.org |
| 7 | * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later |
| 8 | */ |
| 9 | namespace Piwik; |
| 10 | |
| 11 | use Exception; |
| 12 | use Piwik\Db\Schema; |
| 13 | /** |
| 14 | * The ranking query class wraps an arbitrary SQL query with more SQL that limits |
| 15 | * the number of results while aggregating the rest in an a new "Others" row. It also |
| 16 | * allows for some more fancy things that can be configured via method calls of this |
| 17 | * class. The advanced use cases are explained in the doc comments of the methods. |
| 18 | * |
| 19 | * The general use case looks like this: |
| 20 | * |
| 21 | * // limit to 500 rows + "Others" |
| 22 | * $rankingQuery = new RankingQuery(); |
| 23 | * $rankingQuery->setLimit(500); |
| 24 | * |
| 25 | * // idaction_url will be "Others" in the row that contains the aggregated rest |
| 26 | * $rankingQuery->addLabelColumn('idaction_url'); |
| 27 | * |
| 28 | * // the actual query. it's important to sort it before the limit is applied |
| 29 | * $sql = 'SELECT idaction_url, COUNT(*) AS nb_hits |
| 30 | * FROM log_link_visit_action |
| 31 | * GROUP BY idaction_url |
| 32 | * ORDER BY nb_hits DESC'; |
| 33 | * |
| 34 | * // execute the query |
| 35 | * $rankingQuery->execute($sql); |
| 36 | * |
| 37 | * For more examples, see RankingQueryTest.php |
| 38 | * |
| 39 | * @api |
| 40 | */ |
| 41 | class RankingQuery |
| 42 | { |
| 43 | // a special label used to mark the 'Others' row in a ranking query result set. this is mapped to the |
| 44 | // datatable summary row during archiving. |
| 45 | public const LABEL_SUMMARY_ROW = '__mtm_ranking_query_others__'; |
| 46 | /** |
| 47 | * Contains the labels of the inner query. |
| 48 | * Format: "label" => true (to make sure labels don't appear twice) |
| 49 | * @var array |
| 50 | */ |
| 51 | private $labelColumns = array(); |
| 52 | /** |
| 53 | * The columns of the inner query that are not labels |
| 54 | * Format: "label" => "aggregation function" or false for no aggregation |
| 55 | * @var array |
| 56 | */ |
| 57 | private $additionalColumns = array(); |
| 58 | /** |
| 59 | * The limit for each group |
| 60 | * @var int |
| 61 | */ |
| 62 | private $limit = 5; |
| 63 | /** |
| 64 | * The name of the columns that marks rows to be excluded from the limit |
| 65 | * @var string |
| 66 | */ |
| 67 | private $columnToMarkExcludedRows = \false; |
| 68 | /** |
| 69 | * The column that is used to partition the result |
| 70 | * @var bool|string |
| 71 | */ |
| 72 | private $partitionColumn = \false; |
| 73 | /** |
| 74 | * The possible values for the column $this->partitionColumn |
| 75 | * @var array |
| 76 | */ |
| 77 | private $partitionColumnValues = array(); |
| 78 | /** |
| 79 | * The value to use in the label of the 'Others' row. |
| 80 | * @var string |
| 81 | */ |
| 82 | private $othersLabelValue = self::LABEL_SUMMARY_ROW; |
| 83 | /** |
| 84 | * Constructor. |
| 85 | * |
| 86 | * @param int|false $limit The result row limit. See {@link setLimit()}. |
| 87 | */ |
| 88 | public function __construct($limit = \false) |
| 89 | { |
| 90 | if ($limit !== \false) { |
| 91 | $this->setLimit($limit); |
| 92 | } |
| 93 | } |
| 94 | /** |
| 95 | * Set the limit after which everything is grouped to "Others". |
| 96 | * |
| 97 | * @param int $limit |
| 98 | */ |
| 99 | public function setLimit($limit) |
| 100 | { |
| 101 | $this->limit = $limit; |
| 102 | } |
| 103 | /** |
| 104 | * Set the value to use for the label in the 'Others' row. |
| 105 | * |
| 106 | * @param string $value |
| 107 | */ |
| 108 | public function setOthersLabel($value) |
| 109 | { |
| 110 | $this->othersLabelValue = $value; |
| 111 | } |
| 112 | /** |
| 113 | * Add a label column. |
| 114 | * Labels are the columns that are replaced with "Others" after the limit. |
| 115 | * |
| 116 | * @param string|array $labelColumn |
| 117 | */ |
| 118 | public function addLabelColumn($labelColumn) |
| 119 | { |
| 120 | if (is_array($labelColumn)) { |
| 121 | foreach ($labelColumn as $label) { |
| 122 | $this->addLabelColumn($label); |
| 123 | } |
| 124 | return; |
| 125 | } |
| 126 | $this->labelColumns[$labelColumn] = \true; |
| 127 | } |
| 128 | /** |
| 129 | * @return array |
| 130 | */ |
| 131 | public function getLabelColumns() |
| 132 | { |
| 133 | return $this->labelColumns; |
| 134 | } |
| 135 | /** |
| 136 | * Add a column that has be added to the outer queries. |
| 137 | * |
| 138 | * @param $column |
| 139 | * @param string|bool $aggregationFunction If set, this function is used to aggregate the values of "Others", |
| 140 | * eg, `'min'`, `'max'` or `'sum'`. |
| 141 | */ |
| 142 | public function addColumn($column, $aggregationFunction = \false) |
| 143 | { |
| 144 | if (is_array($column)) { |
| 145 | foreach ($column as $c) { |
| 146 | $this->addColumn($c, $aggregationFunction); |
| 147 | } |
| 148 | return; |
| 149 | } |
| 150 | $this->additionalColumns[$column] = $aggregationFunction; |
| 151 | } |
| 152 | /** |
| 153 | * Sets a column that will be used to filter the result into two categories. |
| 154 | * Rows where this column has a value > 0 will be removed from the result and put |
| 155 | * into another array. Both the result and the array of excluded rows are returned |
| 156 | * by {@link execute()}. |
| 157 | * |
| 158 | * @param $column string Name of the column. |
| 159 | * @throws Exception if method is used more than once. |
| 160 | */ |
| 161 | public function setColumnToMarkExcludedRows($column) |
| 162 | { |
| 163 | if ($this->columnToMarkExcludedRows !== \false) { |
| 164 | throw new Exception("setColumnToMarkExcludedRows can only be used once"); |
| 165 | } |
| 166 | $this->columnToMarkExcludedRows = $column; |
| 167 | $this->addColumn($this->columnToMarkExcludedRows); |
| 168 | } |
| 169 | /** |
| 170 | * This method can be used to partition the result based on the possible values of one |
| 171 | * table column. This means the query will split the result set into other sets of rows |
| 172 | * for each possible value you provide (where the rows of each set have a column value |
| 173 | * that equals a possible value). Each of these new sets of rows will be individually |
| 174 | * limited resulting in several limited result sets. |
| 175 | * |
| 176 | * For example, you can run a query aggregating some data on the log_action table and |
| 177 | * partition by log_action.type with the possible values of {@link Piwik\Tracker\Action::TYPE_PAGE_URL}, |
| 178 | * {@link Piwik\Tracker\Action::TYPE_OUTLINK}, {@link Piwik\Tracker\Action::TYPE_DOWNLOAD}. |
| 179 | * The result will be three separate result sets that are aggregated the same ways, but for rows |
| 180 | * where `log_action.type = TYPE_OUTLINK`, for rows where `log_action.type = TYPE_ACTION_URL` and for |
| 181 | * rows `log_action.type = TYPE_DOWNLOAD`. |
| 182 | * |
| 183 | * @param $partitionColumn string The column name to partition by. |
| 184 | * @param $possibleValues Array of possible column values. |
| 185 | * @throws Exception if method is used more than once. |
| 186 | */ |
| 187 | public function partitionResultIntoMultipleGroups($partitionColumn, $possibleValues) |
| 188 | { |
| 189 | if ($this->partitionColumn !== \false) { |
| 190 | throw new Exception("partitionResultIntoMultipleGroups can only be used once"); |
| 191 | } |
| 192 | $this->partitionColumn = $partitionColumn; |
| 193 | $this->partitionColumnValues = $possibleValues; |
| 194 | $this->addColumn($partitionColumn); |
| 195 | } |
| 196 | /** |
| 197 | * Executes the query. |
| 198 | * The object has to be configured first using the other methods. |
| 199 | * |
| 200 | * @param $innerQuery string The "payload" query that does the actual data aggregation. The ordering |
| 201 | * has to be specified in this query. {@link RankingQuery} cannot apply ordering |
| 202 | * itself. |
| 203 | * @param $bind array Bindings for the inner query. |
| 204 | * @param int $timeLimit Adds a MAX_EXECUTION_TIME query hint to the query if $timeLimit > 0 |
| 205 | * for more details see {@link DbHelper::addMaxExecutionTimeHintToQuery} |
| 206 | * @return array The format depends on which methods have been used |
| 207 | * to configure the ranking query. |
| 208 | */ |
| 209 | public function execute($innerQuery, $bind = array(), $timeLimit = 0) |
| 210 | { |
| 211 | $query = $this->generateRankingQuery($innerQuery); |
| 212 | $query = \Piwik\DbHelper::addMaxExecutionTimeHintToQuery($query, $timeLimit); |
| 213 | $data = \Piwik\Db::getReader()->fetchAll($query, $bind); |
| 214 | if ($this->columnToMarkExcludedRows !== \false) { |
| 215 | // split the result into the regular result and the rows with special treatment |
| 216 | $excludedFromLimit = array(); |
| 217 | $result = array(); |
| 218 | foreach ($data as &$row) { |
| 219 | if ($row[$this->columnToMarkExcludedRows] != 0) { |
| 220 | $excludedFromLimit[] = $row; |
| 221 | } else { |
| 222 | $result[] = $row; |
| 223 | } |
| 224 | } |
| 225 | $data = array('result' => &$result, 'excludedFromLimit' => &$excludedFromLimit); |
| 226 | } |
| 227 | if ($this->partitionColumn !== \false) { |
| 228 | if ($this->columnToMarkExcludedRows !== \false) { |
| 229 | $data['result'] = $this->splitPartitions($data['result']); |
| 230 | } else { |
| 231 | $data = $this->splitPartitions($data); |
| 232 | } |
| 233 | } |
| 234 | return $data; |
| 235 | } |
| 236 | private function splitPartitions(&$data) |
| 237 | { |
| 238 | $result = array(); |
| 239 | foreach ($data as &$row) { |
| 240 | $partition = $row[$this->partitionColumn]; |
| 241 | if (!isset($result[$partition])) { |
| 242 | $result[$partition] = array(); |
| 243 | } |
| 244 | $result[$partition][] =& $row; |
| 245 | } |
| 246 | return $result; |
| 247 | } |
| 248 | /** |
| 249 | * Generate the SQL code that does the magic. |
| 250 | * If you want to get the result, use execute() instead. If you want to run the query |
| 251 | * yourself, use this method. |
| 252 | * |
| 253 | * @param $innerQuery string The "payload" query that does the actual data aggregation. The ordering |
| 254 | * has to be specified in this query. {@link RankingQuery} cannot apply ordering |
| 255 | * itself. |
| 256 | * @param $withRollup bool A flag which determines whether to generate the SQL query using ROLLUP |
| 257 | * @return string The entire ranking query SQL. |
| 258 | */ |
| 259 | public function generateRankingQuery($innerQuery, bool $withRollup = \false) |
| 260 | { |
| 261 | // +1 to include "Others" |
| 262 | $limit = $this->limit + 1; |
| 263 | $counterExpression = $this->getCounterExpression($limit, $withRollup); |
| 264 | // generate select clauses for label columns |
| 265 | $labelColumnsString = '`' . implode('`, `', array_keys($this->labelColumns)) . '`'; |
| 266 | $labelColumnsOthersSwitch = array(); |
| 267 | $withRollupColumns = array(); |
| 268 | foreach (array_keys($this->labelColumns) as $column) { |
| 269 | $rollupWhen = ''; |
| 270 | if ($withRollup) { |
| 271 | $rollupLimitValue = empty($withRollupColumns) ? "'" . $this->othersLabelValue . "'" : 'NULL'; |
| 272 | $rollupWhen = "\n WHEN counterRollup = {$limit} THEN {$rollupLimitValue}\n WHEN counterRollup > 0 THEN `{$column}`\n "; |
| 273 | $withRollupColumns[] = $column; |
| 274 | } |
| 275 | $labelColumnsOthersSwitch[] = "\n CASE\n {$rollupWhen}\n WHEN counter = {$limit} THEN '" . $this->othersLabelValue . "'\n ELSE `{$column}`\n END AS `{$column}`\n "; |
| 276 | } |
| 277 | $labelColumnsOthersSwitch = implode(', ', $labelColumnsOthersSwitch); |
| 278 | // generate select clauses for additional columns |
| 279 | $additionalColumnsString = ''; |
| 280 | $additionalColumnsAggregatedString = ''; |
| 281 | foreach ($this->additionalColumns as $additionalColumn => $aggregation) { |
| 282 | $additionalColumnsString .= ', `' . $additionalColumn . '`'; |
| 283 | if ($aggregation !== \false) { |
| 284 | $additionalColumnsAggregatedString .= ', ' . $aggregation . '(`' . $additionalColumn . '`) AS `' . $additionalColumn . '`'; |
| 285 | } else { |
| 286 | $additionalColumnsAggregatedString .= ', `' . $additionalColumn . '`'; |
| 287 | } |
| 288 | } |
| 289 | // initialize the counters |
| 290 | if ($this->partitionColumn !== \false) { |
| 291 | $initCounter = ''; |
| 292 | foreach ($this->partitionColumnValues as $value) { |
| 293 | $initCounter .= '( SELECT @counter' . intval($value) . ':=0 ) initCounter' . intval($value) . ', '; |
| 294 | } |
| 295 | } else { |
| 296 | $initCounter = '( SELECT @counter:=0 ) initCounter,'; |
| 297 | } |
| 298 | $counterRollupExpression = ''; |
| 299 | if ($withRollup && !empty($withRollupColumns)) { |
| 300 | $initCounter .= ' ( SELECT @counterRollup:=0 ) initCounterRollup,'; |
| 301 | $counterRollupWhen = ''; |
| 302 | if (count($withRollupColumns) >= 2) { |
| 303 | $counterRollupWhen = "\n WHEN `" . implode('` IS NULL AND `', $withRollupColumns) . "` IS NULL THEN -1\n "; |
| 304 | } |
| 305 | foreach ($withRollupColumns as $withRollupColumn) { |
| 306 | $counterRollupWhen .= "\n WHEN `{$withRollupColumn}` IS NULL AND @counterRollup = {$limit} THEN {$limit}\n WHEN `{$withRollupColumn}` IS NULL THEN @counterRollup := @counterRollup + 1\n "; |
| 307 | } |
| 308 | $counterRollupExpression = "\n , CASE\n {$counterRollupWhen}\n ELSE 0\n END AS counterRollup\n "; |
| 309 | } |
| 310 | if (\false === strpos($innerQuery, ' LIMIT ') && !Schema::getInstance()->supportsSortingInSubquery()) { |
| 311 | // Setting a limit for the inner query forces the optimizer to use a temporary table, which uses the sorting |
| 312 | $innerQuery .= ' LIMIT 18446744073709551615'; |
| 313 | } |
| 314 | // add a counter to the query |
| 315 | // we rely on the sorting of the inner query |
| 316 | $withCounter = "\n\t\t\tSELECT\n\t\t\t\t{$labelColumnsString},\n\t\t\t\t{$counterExpression} AS counter\n\t\t\t\t{$counterRollupExpression}\n\t\t\t\t{$additionalColumnsString}\n\t\t\tFROM\n\t\t\t\t{$initCounter}\n\t\t\t\t( {$innerQuery} ) actualQuery\n\t\t"; |
| 317 | if ($withRollup && !empty($withRollupColumns) && !Schema::getInstance()->supportsRankingRollupWithoutExtraSorting()) { |
| 318 | // MariaDB requires an additional sorting layer to return |
| 319 | // the counter/counterRollup values we expect |
| 320 | $rollupColumnSorts = []; |
| 321 | foreach ($withRollupColumns as $withRollupColumn) { |
| 322 | $rollupColumnSorts[] = "`{$withRollupColumn}` IS NULL"; |
| 323 | } |
| 324 | $withCounter .= ' ORDER BY ' . implode(', ', $rollupColumnSorts); |
| 325 | $innerQueryOrderBy = \Piwik\DbHelper::extractOrderByFromQuery($innerQuery); |
| 326 | if (null !== $innerQueryOrderBy) { |
| 327 | // copy ORDER BY from inner query to rollup sorting |
| 328 | $withCounter .= ', ' . $innerQueryOrderBy; |
| 329 | } |
| 330 | } |
| 331 | // group by the counter - this groups "Others" because the counter stops at $limit |
| 332 | $groupBy = 'counter'; |
| 333 | if ($withRollup && !empty($counterRollupExpression)) { |
| 334 | $groupBy .= ', counterRollup'; |
| 335 | } |
| 336 | if ($this->partitionColumn !== \false) { |
| 337 | $groupBy .= ', `' . $this->partitionColumn . '`'; |
| 338 | } |
| 339 | $groupOthers = "\n\t\t\tSELECT\n\t\t\t\t{$labelColumnsOthersSwitch}\n\t\t\t\t{$additionalColumnsAggregatedString}\n\t\t\tFROM ( {$withCounter} ) AS withCounter\n\t\t\tGROUP BY {$groupBy}\n\t\t"; |
| 340 | if (!Schema::getInstance()->supportsSortingInSubquery()) { |
| 341 | // When subqueries aren't sorted, we need to sort the result manually again |
| 342 | $groupOthers .= " ORDER BY counter"; |
| 343 | if (!empty($counterRollupExpression)) { |
| 344 | $groupOthers .= ', counterRollup'; |
| 345 | } |
| 346 | } |
| 347 | return $groupOthers; |
| 348 | } |
| 349 | private function getCounterExpression($limit, bool $withRollup = \false) |
| 350 | { |
| 351 | $whens = array(); |
| 352 | if ($this->columnToMarkExcludedRows !== \false) { |
| 353 | // when a row has been specified that marks which records should be excluded |
| 354 | // from limiting, we don't give those rows the normal counter but -1 times the |
| 355 | // value they had before. this way, they have a separate number space (i.e. negative |
| 356 | // integers). |
| 357 | $whens[] = "WHEN {$this->columnToMarkExcludedRows} != 0 THEN -1 * {$this->columnToMarkExcludedRows}"; |
| 358 | } |
| 359 | if ($withRollup) { |
| 360 | foreach (array_keys($this->labelColumns) as $column) { |
| 361 | $whens[] = "WHEN `{$column}` IS NULL THEN -1"; |
| 362 | } |
| 363 | } |
| 364 | if ($this->partitionColumn !== \false) { |
| 365 | // partition: one counter per possible value |
| 366 | foreach ($this->partitionColumnValues as $value) { |
| 367 | $isValue = '`' . $this->partitionColumn . '` = ' . intval($value); |
| 368 | $counter = '@counter' . intval($value); |
| 369 | $whens[] = "WHEN {$isValue} AND {$counter} = {$limit} THEN {$limit}"; |
| 370 | $whens[] = "WHEN {$isValue} THEN {$counter}:={$counter}+1"; |
| 371 | } |
| 372 | $whens[] = "ELSE 0"; |
| 373 | } else { |
| 374 | // no partitioning: add a single counter |
| 375 | $whens[] = "WHEN @counter = {$limit} THEN {$limit}"; |
| 376 | $whens[] = "ELSE @counter:=@counter+1"; |
| 377 | } |
| 378 | return "\n\t\t\tCASE\n\t\t\t\t" . implode("\n\t\t\t\t", $whens) . "\n\t\t\tEND\n\t\t"; |
| 379 | } |
| 380 | } |
| 381 |