/** * @group Core */ public function testPartitionResult() { $query = new RankingQuery(1000); $query->addLabelColumn('label'); $query->partitionResultIntoMultipleGroups('partition', array(1, 2, 3)); $innerQuery = "SELECT label, partition FROM myTable"; $expected = "\n\t\t\tSELECT\n\t\t\t\tCASE\n\t\t\t\t\tWHEN counter = 1001 THEN 'Others'\n\t\t\t\t\tELSE `label`\n\t\t\t\tEND AS `label`,\n\t\t\t\t`partition`\n\t\t\tFROM ( \n\t\t\t\tSELECT\n\t\t\t\t\t`label`,\n\t\t\t\t\tCASE\n\t\t\t\t\t\tWHEN `partition` = 1 AND @counter1 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 1 THEN @counter1:=@counter1+1\n\t\t\t\t\t\tWHEN `partition` = 2 AND @counter2 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 2 THEN @counter2:=@counter2+1\n\t\t\t\t\t\tWHEN `partition` = 3 AND @counter3 = 1001 THEN 1001\n\t\t\t\t\t\tWHEN `partition` = 3 THEN @counter3:=@counter3+1\n\t\t\t\t\t\tELSE 0\n\t\t\t\t\tEND AS counter,\n\t\t\t\t\t`partition`\n\t\t\t\tFROM\n\t\t\t\t\t( SELECT @counter1:=0 ) initCounter1,\n\t\t\t\t\t( SELECT @counter2:=0 ) initCounter2,\n\t\t\t\t\t( SELECT @counter3:=0 ) initCounter3, \n\t\t\t\t\t( SELECT label, partition FROM myTable ) actualQuery\n\t\t\t) AS withCounter\n\t\t\tGROUP BY counter, `partition`\n\t\t"; $this->checkQuery($query, $innerQuery, $expected); }
/** * Time per action */ protected function archiveDayActionsTime($rankingQueryLimit) { $rankingQuery = false; if ($rankingQueryLimit > 0) { $rankingQuery = new RankingQuery($rankingQueryLimit); $rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW); $rankingQuery->addLabelColumn('idaction'); $rankingQuery->addColumn(Metrics::INDEX_PAGE_SUM_TIME_SPENT, 'sum'); $rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($this->actionsTablesByType)); $extraSelects = "log_action.type, log_action.name, count(*) as `" . Metrics::INDEX_PAGE_NB_HITS . "`,"; $from = array("log_link_visit_action", array("table" => "log_action", "joinOn" => "log_link_visit_action.%s = log_action.idaction")); $orderBy = "`" . Metrics::INDEX_PAGE_NB_HITS . "` DESC, log_action.name ASC"; } else { $extraSelects = false; $from = "log_link_visit_action"; $orderBy = false; } $select = "log_link_visit_action.%s as idaction, {$extraSelects}\n\t\t\t\tsum(log_link_visit_action.time_spent_ref_action) as `" . Metrics::INDEX_PAGE_SUM_TIME_SPENT . "`"; $where = "log_link_visit_action.server_time >= ?\n\t\t\t\tAND log_link_visit_action.server_time <= ?\n\t\t \t\tAND log_link_visit_action.idsite = ?\n\t\t \t\tAND log_link_visit_action.time_spent_ref_action > 0\n\t\t \t\tAND log_link_visit_action.%s > 0" . $this->getWhereClauseActionIsNotEvent(); $groupBy = "log_link_visit_action.%s, idaction"; $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_url_ref", $rankingQuery); $this->archiveDayQueryProcess($select, $from, $where, $orderBy, $groupBy, "idaction_name_ref", $rankingQuery); }
/** * Get information about internal referrers (previous pages & loops, i.e. page refreshes) * * @param $idaction * @param $actionType * @param LogAggregator $logAggregator * @param $limitBeforeGrouping * @return array(previousPages:DataTable, loops:integer) */ protected function queryInternalReferrers($idaction, $actionType, $logAggregator, $limitBeforeGrouping = false) { $keyIsOther = 0; $keyIsPageUrlAction = 1; $keyIsSiteSearchAction = 2; $rankingQuery = new RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping); $rankingQuery->addLabelColumn(array('name', 'url_prefix')); $rankingQuery->setColumnToMarkExcludedRows('is_self'); $rankingQuery->partitionResultIntoMultipleGroups('action_partition', array($keyIsOther, $keyIsPageUrlAction, $keyIsSiteSearchAction)); $type = $this->getColumnTypeSuffix($actionType); $mainActionType = Action::TYPE_PAGE_URL; $dimension = 'idaction_url_ref'; if ($actionType == 'title') { $mainActionType = Action::TYPE_PAGE_TITLE; $dimension = 'idaction_name_ref'; } $selects = array('log_action.name', 'log_action.url_prefix', 'CASE WHEN log_link_visit_action.idaction_' . $type . '_ref = ' . intval($idaction) . ' THEN 1 ELSE 0 END AS `is_self`', 'CASE WHEN log_action.type = ' . $mainActionType . ' THEN ' . $keyIsPageUrlAction . ' WHEN log_action.type = ' . Action::TYPE_SITE_SEARCH . ' THEN ' . $keyIsSiteSearchAction . ' ELSE ' . $keyIsOther . ' END AS `action_partition`'); $where = ' log_link_visit_action.idaction_' . $type . ' = ' . intval($idaction); if ($dimension == 'idaction_url_ref') { // site search referrers are logged with url_ref=NULL // when we find one, we have to join on name_ref $dimension = 'IF( idaction_url_ref IS NULL, idaction_name_ref, idaction_url_ref )'; $joinLogActionOn = $dimension; } else { $joinLogActionOn = $dimension; } $metrics = array(Metrics::INDEX_NB_ACTIONS); $data = $logAggregator->queryActionsByDimension(array($dimension), $where, $selects, $metrics, $rankingQuery, $joinLogActionOn); $loops = 0; $nbPageviews = 0; $previousPagesDataTable = new DataTable(); if (isset($data['result'][$keyIsPageUrlAction])) { foreach ($data['result'][$keyIsPageUrlAction] as &$page) { $nbActions = intval($page[Metrics::INDEX_NB_ACTIONS]); $previousPagesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $this->getPageLabel($page, Action::TYPE_PAGE_URL), Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } $previousSearchesDataTable = new DataTable(); if (isset($data['result'][$keyIsSiteSearchAction])) { foreach ($data['result'][$keyIsSiteSearchAction] as &$search) { $nbActions = intval($search[Metrics::INDEX_NB_ACTIONS]); $previousSearchesDataTable->addRow(new Row(array(Row::COLUMNS => array('label' => $search['name'], Metrics::INDEX_NB_ACTIONS => $nbActions)))); $nbPageviews += $nbActions; } } if (isset($data['result'][0])) { foreach ($data['result'][0] as &$referrer) { $nbPageviews += intval($referrer[Metrics::INDEX_NB_ACTIONS]); } } if (count($data['excludedFromLimit'])) { $loops += intval($data['excludedFromLimit'][0][Metrics::INDEX_NB_ACTIONS]); $nbPageviews += $loops; } return array('pageviews' => $nbPageviews, 'previousPages' => $previousPagesDataTable, 'previousSiteSearches' => $previousSearchesDataTable, 'loops' => $loops); }
private function aggregateDayInteractions() { $select = "\n log_action_content_name.name as contentName,\n log_action_content_interaction.name as contentInteraction,\n log_action_content_piece.name as contentPiece,\n\n\t\t\t\tcount(*) as `" . Metrics::INDEX_CONTENT_NB_INTERACTIONS . "`\n "; $from = array("log_link_visit_action", array("table" => "log_action", "tableAlias" => "log_action_content_piece", "joinOn" => "log_link_visit_action.idaction_content_piece = log_action_content_piece.idaction"), array("table" => "log_action", "tableAlias" => "log_action_content_interaction", "joinOn" => "log_link_visit_action.idaction_content_interaction = log_action_content_interaction.idaction"), array("table" => "log_action", "tableAlias" => "log_action_content_name", "joinOn" => "log_link_visit_action.idaction_content_name = log_action_content_name.idaction")); $where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.idaction_content_name IS NOT NULL\n AND log_link_visit_action.idaction_content_interaction IS NOT NULL"; $groupBy = "log_action_content_piece.idaction,\n log_action_content_interaction.idaction,\n log_action_content_name.idaction"; $orderBy = "`" . Metrics::INDEX_CONTENT_NB_INTERACTIONS . "` DESC"; $rankingQueryLimit = ArchivingHelper::getRankingQueryLimit(); $rankingQuery = null; if ($rankingQueryLimit > 0) { $rankingQuery = new RankingQuery($rankingQueryLimit); $rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW); $rankingQuery->addLabelColumn(array('contentPiece', 'contentInteraction', 'contentName')); $rankingQuery->addColumn(array(Metrics::INDEX_CONTENT_NB_INTERACTIONS), 'sum'); } $resultSet = $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery); while ($row = $resultSet->fetch()) { $this->aggregateInteractionRow($row); } }
protected function aggregateDayEvents() { $select = "\n log_action_event_category.name as eventCategory,\n log_action_event_action.name as eventAction,\n log_action_event_name.name as eventName,\n\n\t\t\t\tcount(distinct log_link_visit_action.idvisit) as `" . Metrics::INDEX_NB_VISITS . "`,\n\t\t\t\tcount(distinct log_link_visit_action.idvisitor) as `" . Metrics::INDEX_NB_UNIQ_VISITORS . "`,\n\t\t\t\tcount(*) as `" . Metrics::INDEX_EVENT_NB_HITS . "`,\n\n\t\t\t\tsum(\n\t\t\t\t\tcase when " . Action::DB_COLUMN_CUSTOM_FLOAT . " is null\n\t\t\t\t\t\tthen 0\n\t\t\t\t\t\telse " . Action::DB_COLUMN_CUSTOM_FLOAT . "\n\t\t\t\t\tend\n\t\t\t\t) as `" . Metrics::INDEX_EVENT_SUM_EVENT_VALUE . "`,\n\t\t\t\tsum( case when " . Action::DB_COLUMN_CUSTOM_FLOAT . " is null then 0 else 1 end )\n\t\t\t\t as `" . Metrics::INDEX_EVENT_NB_HITS_WITH_VALUE . "`,\n\t\t\t\tmin(" . Action::DB_COLUMN_CUSTOM_FLOAT . ") as `" . Metrics::INDEX_EVENT_MIN_EVENT_VALUE . "`,\n\t\t\t\tmax(" . Action::DB_COLUMN_CUSTOM_FLOAT . ") as `" . Metrics::INDEX_EVENT_MAX_EVENT_VALUE . "`\n "; $from = array("log_link_visit_action", array("table" => "log_action", "tableAlias" => "log_action_event_category", "joinOn" => "log_link_visit_action.idaction_event_category = log_action_event_category.idaction"), array("table" => "log_action", "tableAlias" => "log_action_event_action", "joinOn" => "log_link_visit_action.idaction_event_action = log_action_event_action.idaction"), array("table" => "log_action", "tableAlias" => "log_action_event_name", "joinOn" => "log_link_visit_action.idaction_name = log_action_event_name.idaction")); $where = "log_link_visit_action.server_time >= ?\n AND log_link_visit_action.server_time <= ?\n AND log_link_visit_action.idsite = ?\n AND log_link_visit_action.idaction_event_category IS NOT NULL"; $groupBy = "log_action_event_category.idaction,\n log_action_event_action.idaction,\n log_action_event_name.idaction"; $orderBy = "`" . Metrics::INDEX_NB_VISITS . "` DESC"; $rankingQueryLimit = ArchivingHelper::getRankingQueryLimit(); $rankingQuery = null; if ($rankingQueryLimit > 0) { $rankingQuery = new RankingQuery($rankingQueryLimit); $rankingQuery->setOthersLabel(DataTable::LABEL_SUMMARY_ROW); $rankingQuery->addLabelColumn(array('eventCategory', 'eventAction', 'eventName')); $rankingQuery->addColumn(array(Metrics::INDEX_NB_UNIQ_VISITORS)); $rankingQuery->addColumn(array(Metrics::INDEX_EVENT_NB_HITS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_EVENT_NB_HITS_WITH_VALUE), 'sum'); $rankingQuery->addColumn(Metrics::INDEX_EVENT_SUM_EVENT_VALUE, 'sum'); $rankingQuery->addColumn(Metrics::INDEX_EVENT_MIN_EVENT_VALUE, 'min'); $rankingQuery->addColumn(Metrics::INDEX_EVENT_MAX_EVENT_VALUE, 'max'); } $this->archiveDayQueryProcess($select, $from, $where, $groupBy, $orderBy, $rankingQuery); }