douchanxiu5636 2017-03-22 07:01
浏览 408
已采纳

根据日期范围从月表MySQL中获取数据(2016年1月1日至2016年4月1日之间的前数据)

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

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

  • 写回答

4条回答 默认 最新

  • dsl2014 2017-03-22 11:25
    关注

    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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果