根据日期范围从月表 MySQL 中获取数据(2016 年 1 月 1 日至 2016 年 4 月 1 日之间的前数据) [英] Fetch data from monthly table MySQL based on date range (ex-data between 01-Jan-2016 to 01-April-2016)

问题描述

我有每月数据库表，用于存储我网站的每日用户注册计数.在我的 UI 设计中，我有两个日期之间的日期范围选择器我必须获得提供的日期范围之间的注册用户总和.(但会有 4 或 5日期范围之间的几个月(基于日期范围))我将如何获取数据?我需要优化的解决方案，以便从多个 mysql 表中获取数据总和.

I have monthly database tables which store daily user signup count of my website.in my UI design I have daterange piker between two date I have to get sum of signup user between date range provided .(But there will be 4 OR 5 many month between date range (based on date range)) How I will get data ? I need optimised solution so I will get that sum of data from multiple mysql tables.

CREATE TABLE daily_analytics_01_2017 ( 
id int(11) NOT NULL AUTO_INCREMENT, 
country varchar(255) DEFAULT NULL, 
device varchar(255) DEFAULT NULL, 
browser varchar(255) DEFAULT NULL, 
gender varchar(255) DEFAULT NULL, u
ser_loginCount int(11) NOT NULL, 
user_signup_count int(11) NOT NULL, 
tracking_date date NOT NULL, 
PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

上表为每个月的名称，如

Above table for each month name like

daily_analytics_MONTH_YEAR

daily_analytics_MONTH_YEAR

除此之外，我还必须每月跟踪所有登录数据和我网站上的其他操作.

along with this all data for login and other action on my site I have to track on monthly basis .

推荐答案

使用以下函数构建查询:

Use Following Function to built query:

function getMonthWiseQuery($fromdate,$todate)
{

    $fromexplode=explode("-",$fromdate);
    $startyear=$fromexplode[0];
    $startmonth=$fromexplode[1];
    $startdate=$fromexplode[2];


    $toexplode=explode("-",$todate);
    $endyear=$toexplode[0];
    $endmonth=$toexplode[1];
    $enddate=$toexplode[2];

    $queryBuild=array();
    $startmonthnew = $startmonth;
    $count=0;
    for ($i = $startyear; $i <= $endyear; $i++) {
        for ($j = $startmonthnew; $j < 13; $j++) {

                $count++;
                if ($fromdate) {

                    if (strlen($j) == 1) {
                        $j = "0" . $j;
                    }
                    if($count!=1)
                    {
                        $query=" UNION ALL";
                        $query.= " select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_{$j}_{$i} where tracking_date BETWEEN \"{$fromdate}\" and \"{$todate}\" ";
                    }else{
                        $query = "select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_{$j}_{$i} where tracking_date BETWEEN \"{$fromdate}\" and \"{$todate}\" ";
                    }

                    array_push($queryBuild,$query);

                }

            if ($j == 12) {
                $startmonthnew = 1;
                break;
            }

            if ($endyear == $i) {
                if ($j == $endmonth) {
                    break;
                }
            }

        }
    }

return implode(" ",$queryBuild);
}

函数将返回日期范围内所有表的联合查询-

Function will return union query with all table in date range-

    select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_01_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_02_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_03_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21" 

然后从这个查询中我们可以从表的联合中获取总结果的总和-

Then from this query we can fetch sum of total result from UNion of tables-

   select sum(Total_UserBy_referral) as Total_UserBy_referral , sum(TotalUnverifiedUsers) as TotalUnverifiedUsers,sum(TotalRegisteredUsers) as TotalRegisteredUsers, sum(TotalActivatedUsers) as TotalActivatedUsers from (
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_01_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_02_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21"  
UNION ALL 
select sum(user_with_referral) as Total_UserBy_referral,sum(total_unverified_users) as TotalUnverifiedUsers,sum(user_signup_count) as TotalRegisteredUsers,sum(user_signUpactivationSuccess_count) as TotalActivatedUsers from daily_analytics_03_2017 where tracking_date BETWEEN "2017-01-01" and "2017-03-21" 
) as t

这篇关于根据日期范围从月表 MySQL 中获取数据(2016 年 1 月 1 日至 2016 年 4 月 1 日之间的前数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！