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