In this example i am going show you some MySql helpful queries to display any user or product analytic reports about your products performance.
Suppose you have a product base website and you have to calculate the number of clicks and unique clicks on product, Or you have to display a graph view of monthly or yearly clicks or unique clicks, For that purpose these queries are very useful.
Here i have a product analytic table with some analytic data.
product_analytic
| Product_Id | Click | IP | Created | Updated |
|---|---|---|---|---|
| 1 | 1 | 192.168.1.1 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 1 | 1 | 192.168.1.1 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 2 | 1 | 192.168.2.1 | 2016-01-02 00:00:00 | 2016-01-02 00:00:00 |
| .. | .. | 1.. | … | … |
| .. | .. | 1.. | … | … |
| .. | .. | 1.. | … | … |
From this table i have to fetch clicks and uniques clicks on product day, week, month, year wise.
Fetching data by daily
SELECT
DATE(created) AS date,
COUNT(click) AS click,
COUNT(DISTINCT(ip)) AS unique_click
FROM product_analytic
WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
GROUP BY date
ORDER BY date
|
OUTPUT:
Fetching data by weekly
SELECT
DATE_FORMAT(created, '%X-%V') AS date,
COUNT(click) AS click,
COUNT(DISTINCT(ip)) AS unique_click
FROM product_analytic
WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
GROUP BY date
ORDER BY date
|
Fetching data by monthly
SELECT
DATE_FORMAT(created, '%Y-%m') AS date,
COUNT(click) AS click,
COUNT(DISTINCT(ip)) AS unique_click
FROM product_analytic
WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
GROUP BY date
ORDER BY date
|
Fetching data by yearly
SELECT
DATE_FORMAT(created, '%Y') AS date,
COUNT(click) AS click,
COUNT(DISTINCT(ip)) AS unique_click
FROM product_analytic
WHERE created BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
GROUP BY date
ORDER BY date
|
