PHP 클래스 Piwik\DataAccess\LogAggregator

You can use the methods in this class within {@link Piwik\Plugin\Archiver Archiver} descendants to aggregate log data without having to write SQL queries. ### Aggregation Dimension All aggregation methods accept a **dimension** parameter. These parameters are important as they control how rows in a table are aggregated together. A **_dimension_** is just a table column. Rows that have the same values for these columns are aggregated together. The result of these aggregations is a set of metrics for every recorded value of a **dimension**. _Note: A dimension is essentially the same as a **GROUP BY** field._ ### Examples **Aggregating visit data** $archiveProcessor = // ... $logAggregator = $archiveProcessor->getLogAggregator(); get metrics for every used browser language of all visits by returning visitors $query = $logAggregator->queryVisitsByDimension( $dimensions = array('log_visit.location_browser_lang'), $where = 'log_visit.visitor_returning = 1', also count visits for each browser language that are not located in the US $additionalSelects = array('sum(case when log_visit.location_country <> 'us' then 1 else 0 end) as nonus'), we're only interested in visits, unique visitors & actions, so don't waste time calculating anything else $metrics = array(Metrics::INDEX_NB_UNIQ_VISITORS, Metrics::INDEX_NB_VISITS, Metrics::INDEX_NB_ACTIONS), ); if ($query === false) { return; } while ($row = $query->fetch()) { $uniqueVisitors = $row[Metrics::INDEX_NB_UNIQ_VISITORS]; $visits = $row[Metrics::INDEX_NB_VISITS]; $actions = $row[Metrics::INDEX_NB_ACTIONS]; ... do something w/ calculated metrics ... } **Aggregating conversion data** $archiveProcessor = // ... $logAggregator = $archiveProcessor->getLogAggregator(); get metrics for ecommerce conversions for each country $query = $logAggregator->queryConversionsByDimension( $dimensions = array('log_conversion.location_country'), $where = 'log_conversion.idgoal = 0', // 0 is the special ecommerceOrder idGoal value in the table also calculate average tax and max shipping per country $additionalSelects = array( 'AVG(log_conversion.revenue_tax) as avg_tax', 'MAX(log_conversion.revenue_shipping) as max_shipping' ) ); if ($query === false) { return; } while ($row = $query->fetch()) { $country = $row['location_country']; $numEcommerceSales = $row[Metrics::INDEX_GOAL_NB_CONVERSIONS]; $numVisitsWithEcommerceSales = $row[Metrics::INDEX_GOAL_NB_VISITS_CONVERTED]; $avgTaxForCountry = $row['avg_tax']; $maxShippingForCountry = $row['max_shipping']; ... do something with aggregated data ... }
파일 보기 프로젝트 열기: piwik/piwik 1 사용 예제들

보호된 프로퍼티들

프로퍼티 타입 설명
$dateEnd Piwik\Date
$dateStart Piwik\Date
$segment Piwik\Segment
$sites int[]

공개 메소드들

메소드 설명
__construct ( Piwik\ArchiveProcessor\Parameters $params ) Constructor.
generateQuery ( $select, $from, $where, $groupBy, $orderBy, $limit, $offset )
getConversionsMetricFields ( )
getDb ( )
getMetricsFromVisitByDimension ( string $dimension ) : Piwik\DataArray Helper function that returns an array with common metrics for a given log_visit field distinct values.
getSelectsFromRangedColumn ( string $column, array $ranges, string $table, string $selectColumnPrefix, boolean $restrictToReturningVisitors = false ) : array Creates and returns an array of SQL SELECT expressions that will each count how many rows have a column whose value is within a certain range.
getSqlRevenue ( $field )
makeArrayOneColumn ( $row, $columnName, boolean $lookForThisPrefix = false ) : array Clean up the row data and return values.
queryActionsByDimension ( array | string $dimensions, boolean | string $where = '', array $additionalSelects = [], boolean | array $metrics = false, boolean | Piwik\RankingQuery $rankingQuery = null, boolean | string $joinLogActionOnColumn = false ) : mixed Executes and returns a query aggregating action data (everything in the log_action table) and returns a DB statement that can be used to iterate over the result
queryConversionsByDimension ( array | string $dimensions = [], boolean | string $where = false, array $additionalSelects = [] ) : Zend_Db_Statement Executes a query aggregating conversion data (everything in the **log_conversion** table) and returns a DB statement that can be used to iterate over the result.
queryEcommerceItems ( string $dimension ) : Zend_Db_Statement Executes and returns a query aggregating ecommerce item data (everything stored in the **log\_conversion\_item** table) and returns a DB statement that can be used to iterate over the result
queryVisitsByDimension ( array $dimensions = [], boolean | string $where = false, array $additionalSelects = [], boolean | array $metrics = false, boolean | Piwik\RankingQuery $rankingQuery = false ) : mixed Executes and returns a query aggregating visit logs, optionally grouping by some dimension. Returns a DB statement that can be used to iterate over the result
setQueryOriginHint ( $nameOfOrigiin )

보호된 메소드들

메소드 설명
getActionsMetricFields ( )
getDimensionsToSelect ( $dimensions, array $additionalSelects ) : array Will return the subset of $dimensions that are not found in $additionalSelects
getGeneralQueryBindParams ( ) : array Returns general bind parameters for all log aggregation queries. This includes the datetime start of entities, datetime end of entities and IDs of all sites.
getGroupByStatement ( $dimensions, $tableName )
getSelectAliasAs ( $metricId )
getSelectDimensions ( $dimensions, $tableName, boolean $appendSelectAs = true ) : mixed Returns the dimensions array, where (1) the table name is prepended to the field (2) the "AS label " is appended to the field
getSelectStatement ( $dimensions, $tableName, $additionalSelects, array $availableMetrics, $requestedMetrics = false )
getSelectsMetrics ( $metricsAvailable, $metricsRequested = false )
getVisitsMetricFields ( )
getWhereStatement ( $tableName, $datetimeField, $extraWhere = false )
isFieldFunctionOrComplexExpression ( $field )
isMetricRequested ( $metricId, $metricsRequested )

비공개 메소드들

메소드 설명
getSqlConversionRevenueSum ( $field )
prefixColumn ( string $column, string $tableName ) : string Prefixes a column name with a table name if not already done.

메소드 상세

__construct() 공개 메소드

Constructor.
public __construct ( Piwik\ArchiveProcessor\Parameters $params )
$params Piwik\ArchiveProcessor\Parameters

generateQuery() 공개 메소드

public generateQuery ( $select, $from, $where, $groupBy, $orderBy, $limit, $offset )

getActionsMetricFields() 보호된 메소드

protected getActionsMetricFields ( )

getConversionsMetricFields() 공개 정적인 메소드

public static getConversionsMetricFields ( )

getDb() 공개 메소드

public getDb ( )

getDimensionsToSelect() 보호된 메소드

Will return the subset of $dimensions that are not found in $additionalSelects
protected getDimensionsToSelect ( $dimensions, array $additionalSelects ) : array
$dimensions
$additionalSelects array
리턴 array

getGeneralQueryBindParams() 보호된 메소드

Returns general bind parameters for all log aggregation queries. This includes the datetime start of entities, datetime end of entities and IDs of all sites.
protected getGeneralQueryBindParams ( ) : array
리턴 array

getGroupByStatement() 보호된 메소드

protected getGroupByStatement ( $dimensions, $tableName )

getMetricsFromVisitByDimension() 공개 메소드

The statistics returned are: - number of unique visitors - number of visits - number of actions - maximum number of action for a visit - sum of the visits' length in sec - count of bouncing visits (visits with one page view) For example if $dimension = 'config_os' it will return the statistics for every distinct Operating systems The returned array will have a row per distinct operating systems, and a column per stat (nb of visits, max actions, etc) 'label' Metrics::INDEX_NB_UNIQ_VISITORS Metrics::INDEX_NB_VISITS etc. Linux 27 66 ... Windows XP 12 ... Mac OS 15 36 ...
public getMetricsFromVisitByDimension ( string $dimension ) : Piwik\DataArray
$dimension string Table log_visit field name to be use to compute common stats
리턴 Piwik\DataArray

getSelectAliasAs() 보호된 메소드

protected getSelectAliasAs ( $metricId )

getSelectDimensions() 보호된 메소드

Returns the dimensions array, where (1) the table name is prepended to the field (2) the "AS label " is appended to the field
protected getSelectDimensions ( $dimensions, $tableName, boolean $appendSelectAs = true ) : mixed
$dimensions
$tableName
$appendSelectAs boolean
리턴 mixed

getSelectStatement() 보호된 메소드

protected getSelectStatement ( $dimensions, $tableName, $additionalSelects, array $availableMetrics, $requestedMetrics = false )
$availableMetrics array

getSelectsFromRangedColumn() 공개 정적인 메소드

**Note:** The result of this function is meant for use in the $additionalSelects parameter in one of the query... methods (for example {@link queryVisitsByDimension()}). **Example** summarize one column $visitTotalActionsRanges = array( array(1, 1), array(2, 10), array(10) ); $selects = LogAggregator::getSelectsFromRangedColumn('visit_total_actions', $visitTotalActionsRanges, 'log_visit', 'vta'); summarize another column in the same request $visitCountVisitsRanges = array( array(1, 1), array(2, 20), array(20) ); $selects = array_merge( $selects, LogAggregator::getSelectsFromRangedColumn('visitor_count_visits', $visitCountVisitsRanges, 'log_visit', 'vcv') ); perform the query $logAggregator = // get the LogAggregator somehow $query = $logAggregator->queryVisitsByDimension($dimensions = array(), $where = false, $selects); $tableSummary = $query->fetch(); $numberOfVisitsWithOneAction = $tableSummary['vta0']; $numberOfVisitsBetweenTwoAnd10 = $tableSummary['vta1']; $numberOfVisitsWithVisitCountOfOne = $tableSummary['vcv0'];
public static getSelectsFromRangedColumn ( string $column, array $ranges, string $table, string $selectColumnPrefix, boolean $restrictToReturningVisitors = false ) : array
$column string The name of a column in `$table` that will be summarized.
$ranges array The array of ranges over which the data in the table will be summarized. For example, ``` array( array(1, 1), array(2, 2), array(3, 8), array(8) // everything over 8 ) ```
$table string The unprefixed name of the table whose rows will be summarized.
$selectColumnPrefix string The prefix to prepend to each SELECT expression. This prefix is used to differentiate different sets of range summarization SELECTs. You can supply different values to this argument to summarize several columns in one query (see above for an example).
$restrictToReturningVisitors boolean Whether to only summarize rows that belong to visits of returning visitors or not. If this argument is true, then the SELECT expressions returned can only be used with the {@link queryVisitsByDimension()} method.
리턴 array An array of SQL SELECT expressions, for example, ``` array( 'sum(case when log_visit.visit_total_actions between 0 and 2 then 1 else 0 end) as vta0', 'sum(case when log_visit.visit_total_actions > 2 then 1 else 0 end) as vta1' ) ```

getSelectsMetrics() 보호된 메소드

protected getSelectsMetrics ( $metricsAvailable, $metricsRequested = false )

getSqlRevenue() 공개 정적인 메소드

public static getSqlRevenue ( $field )

getVisitsMetricFields() 보호된 메소드

protected getVisitsMetricFields ( )

getWhereStatement() 보호된 메소드

protected getWhereStatement ( $tableName, $datetimeField, $extraWhere = false )

isFieldFunctionOrComplexExpression() 보호된 메소드

protected isFieldFunctionOrComplexExpression ( $field )

isMetricRequested() 보호된 메소드

protected isMetricRequested ( $metricId, $metricsRequested )

makeArrayOneColumn() 공개 정적인 메소드

$lookForThisPrefix can be used to make sure only SOME of the data in $row is used. The array will have one column $columnName
public static makeArrayOneColumn ( $row, $columnName, boolean $lookForThisPrefix = false ) : array
$row
$columnName
$lookForThisPrefix boolean A string that identifies which elements of $row to use in the result. Every key of $row that starts with this value is used.
리턴 array

queryActionsByDimension() 공개 메소드

**Result Set** Each row of the result set represents an aggregated group of actions. The following columns are in each aggregate row: - **{@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS}**: The total number of unique visitors that performed the actions in this group. - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits these actions belong to. - **{@link Piwik\Metrics::INDEX_NB_ACTIONS}**: The total number of actions in this aggregate group. Additional data can be selected through the $additionalSelects parameter. _Note: The metrics calculated by this query can be customized by the $metrics parameter._
public queryActionsByDimension ( array | string $dimensions, boolean | string $where = '', array $additionalSelects = [], boolean | array $metrics = false, boolean | Piwik\RankingQuery $rankingQuery = null, boolean | string $joinLogActionOnColumn = false ) : mixed
$dimensions array | string One or more SELECT fields that will be used to group the log_action rows by. This parameter determines which log_action rows will be aggregated together.
$where boolean | string Additional condition for the WHERE clause. Can be used to filter the set of visits that are considered for aggregation.
$additionalSelects array Additional SELECT fields that are not included in the group by clause. These can be aggregate expressions, eg, `SUM(somecol)`.
$metrics boolean | array The set of metrics to calculate and return. If `false`, the query will select all of them. The following values can be used: - {@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS} - {@link Piwik\Metrics::INDEX_NB_VISITS} - {@link Piwik\Metrics::INDEX_NB_ACTIONS}
$rankingQuery boolean | Piwik\RankingQuery A pre-configured ranking query instance that will be used to limit the result. If set, the return value is the array returned by {@link Piwik\RankingQuery::execute()}.
$joinLogActionOnColumn boolean | string One or more columns from the **log_link_visit_action** table that log_action should be joined on. The table alias used for each join is `"log_action$i"` where `$i` is the index of the column in this array. If a string is used for this parameter, the table alias is not suffixed (since there is only one column).
리턴 mixed A Zend_Db_Statement if `$rankingQuery` isn't supplied, otherwise the result of {@link Piwik\RankingQuery::execute()}. Read [this](#queryEcommerceItems-result-set) to see what aggregate data is calculated by the query.

queryConversionsByDimension() 공개 메소드

**Result Set** Each row of the result set represents an aggregated group of conversions. The following columns are in each aggregate row: - **{@link Piwik\Metrics::INDEX_GOAL_NB_CONVERSIONS}**: The total number of conversions in this aggregate group. - **{@link Piwik\Metrics::INDEX_GOAL_NB_VISITS_CONVERTED}**: The total number of visits during which these conversions were converted. - **{@link Piwik\Metrics::INDEX_GOAL_REVENUE}**: The total revenue generated by these conversions. This value includes the revenue from individual ecommerce items. - **{@link Piwik\Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SUBTOTAL}**: The total cost of all ecommerce items sold within these conversions. This value does not include tax, shipping or any applied discount. _This metric is only applicable to the special **ecommerce** goal (where idGoal == 'ecommerceOrder')._ - **{@link Piwik\Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_TAX}**: The total tax applied to every transaction in these conversions. _This metric is only applicable to the special **ecommerce** goal (where idGoal == 'ecommerceOrder')._ - **{@link Piwik\Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_SHIPPING}**: The total shipping cost for every transaction in these conversions. _This metric is only applicable to the special **ecommerce** goal (where idGoal == 'ecommerceOrder')._ - **{@link Piwik\Metrics::INDEX_GOAL_ECOMMERCE_REVENUE_DISCOUNT}**: The total discount applied to every transaction in these conversions. _This metric is only applicable to the special **ecommerce** goal (where idGoal == 'ecommerceOrder')._ - **{@link Piwik\Metrics::INDEX_GOAL_ECOMMERCE_ITEMS}**: The total number of ecommerce items sold in each transaction in these conversions. _This metric is only applicable to the special **ecommerce** goal (where idGoal == 'ecommerceOrder')._ Additional data can be selected through the $additionalSelects parameter. _Note: This method will only query the **log_conversion** table. Other tables cannot be joined using this method._
public queryConversionsByDimension ( array | string $dimensions = [], boolean | string $where = false, array $additionalSelects = [] ) : Zend_Db_Statement
$dimensions array | string One or more **SELECT** fields that will be used to group the log_conversion rows by. This parameter determines which **log_conversion** rows will be aggregated together.
$where boolean | string An optional SQL expression used in the SQL's **WHERE** clause.
$additionalSelects array Additional SELECT fields that are not included in the group by clause. These can be aggregate expressions, eg, `SUM(somecol)`.
리턴 Zend_Db_Statement

queryEcommerceItems() 공개 메소드

**Result Set** Each row of the result set represents an aggregated group of ecommerce items. The following columns are in each row of the result set: - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_REVENUE}**: The total revenue for the group of items. - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_QUANTITY}**: The total number of items in this group. - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_PRICE}**: The total price for the group of items. - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ORDERS}**: The total number of orders this group of items belongs to. This will be <= to the total number of items in this group. - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits that caused these items to be logged. - **ecommerceType**: Either {@link Piwik\Tracker\GoalManager::IDGOAL_CART} if the items in this group were abandoned by a visitor, or {@link Piwik\Tracker\GoalManager::IDGOAL_ORDER} if they were ordered by a visitor. **Limitations** Segmentation is not yet supported for this aggregation method.
public queryEcommerceItems ( string $dimension ) : Zend_Db_Statement
$dimension string One or more **log\_conversion\_item** columns to group aggregated data by. Eg, `'idaction_sku'` or `'idaction_sku, idaction_category'`.
리턴 Zend_Db_Statement A statement object that can be used to iterate through the query's result set. See [above](#queryEcommerceItems-result-set) to learn more about what this query selects.

queryVisitsByDimension() 공개 메소드

**Result Set** The following columns are in each row of the result set: - **{@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS}**: The total number of unique visitors in this group of aggregated visits. - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits aggregated. - **{@link Piwik\Metrics::INDEX_NB_ACTIONS}**: The total number of actions performed in this group of aggregated visits. - **{@link Piwik\Metrics::INDEX_MAX_ACTIONS}**: The maximum actions perfomred in one visit for this group of visits. - **{@link Piwik\Metrics::INDEX_SUM_VISIT_LENGTH}**: The total amount of time spent on the site for this group of visits. - **{@link Piwik\Metrics::INDEX_BOUNCE_COUNT}**: The total number of bounced visits in this group of visits. - **{@link Piwik\Metrics::INDEX_NB_VISITS_CONVERTED}**: The total number of visits for which at least one conversion occurred, for this group of visits. Additional data can be selected by setting the $additionalSelects parameter. _Note: The metrics returned by this query can be customized by the $metrics parameter._
public queryVisitsByDimension ( array $dimensions = [], boolean | string $where = false, array $additionalSelects = [], boolean | array $metrics = false, boolean | Piwik\RankingQuery $rankingQuery = false ) : mixed
$dimensions array `SELECT` fields (or just one field) that will be grouped by, eg, `'referrer_name'` or `array('referrer_name', 'referrer_keyword')`. The metrics retrieved from the query will be specific to combinations of these fields. So if `array('referrer_name', 'referrer_keyword')` is supplied, the query will aggregate visits for each referrer/keyword combination.
$where boolean | string Additional condition for the `WHERE` clause. Can be used to filter the set of visits that are considered for aggregation.
$additionalSelects array Additional `SELECT` fields that are not included in the group by clause. These can be aggregate expressions, eg, `SUM(somecol)`.
$metrics boolean | array The set of metrics to calculate and return. If false, the query will select all of them. The following values can be used: - {@link Piwik\Metrics::INDEX_NB_UNIQ_VISITORS} - {@link Piwik\Metrics::INDEX_NB_VISITS} - {@link Piwik\Metrics::INDEX_NB_ACTIONS} - {@link Piwik\Metrics::INDEX_MAX_ACTIONS} - {@link Piwik\Metrics::INDEX_SUM_VISIT_LENGTH} - {@link Piwik\Metrics::INDEX_BOUNCE_COUNT} - {@link Piwik\Metrics::INDEX_NB_VISITS_CONVERTED}
$rankingQuery boolean | Piwik\RankingQuery A pre-configured ranking query instance that will be used to limit the result. If set, the return value is the array returned by {@link Piwik\RankingQuery::execute()}.
리턴 mixed A Zend_Db_Statement if `$rankingQuery` isn't supplied, otherwise the result of {@link Piwik\RankingQuery::execute()}. Read {@link queryVisitsByDimension() this} to see what aggregate data is calculated by the query.

setQueryOriginHint() 공개 메소드

public setQueryOriginHint ( $nameOfOrigiin )

프로퍼티 상세

$dateEnd 보호되어 있는 프로퍼티

protected Date,Piwik $dateEnd
리턴 Piwik\Date

$dateStart 보호되어 있는 프로퍼티

protected Date,Piwik $dateStart
리턴 Piwik\Date

$segment 보호되어 있는 프로퍼티

protected Segment,Piwik $segment
리턴 Piwik\Segment

$sites 보호되어 있는 프로퍼티

protected int[] $sites
리턴 int[]