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

我有月度数据库表,用于存储我的网站的每日用户注册次数。在我的UI设计中我有日期范围之间的piker 两个日期我必须在提供的日期范围之间获得注册用户的总和。(但是在日期范围之间(基于日期范围),每月将有4个OR 5)我将如何获取数据? 我需要优化的解决方案,所以我将从多个mysql表中获取数据总和。</ p>

  CREATE TABLE daily_analytics_01_2017(
id int(11)NOT NULL AUTO_INCREMENT,\ ncountry 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;
</ code> </ pre>

每月上表 名称如</ p>

daily_analytics_MONTH_YEAR </ p>

以及我网站上的登录和其他操作的所有数据我必须每月跟踪。</ p>

</ div>

展开原文

原文

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 .

douke9379
douke9379 是的但是我还需要为注册,登录,设置密码等其他用户管理任务维护其他分析。在每日基础上。
3 年多之前 回复
dras2334
dras2334 从该代码看,您似乎正在为每个月的数据创建一个表。多数民众赞成会增加赌桌数量。
3 年多之前 回复
dounue6984
dounue6984 CREATETABLEdaily_analytics_01_2017(idint(11)NOTNULLAUTO_INCREMENT,countryvarchar(255)DEFAULTNULL,devicevarchar(255)DEFAULTNULL,browservarchar(255)DEFAULTNULL,gendervarchar(255)DEFAULTNULL,user_loginCountint(11)NOTNULL,user_signup_countint(11)NOTNULL,tracking_datedateNOTNULL,PRIMARYKEY(id))ENGINE=InnoDBAUTO_INCREMENT=16DEFAULTCHARSET=latin1;上面的表格为每个月的名称,例如daily_analytics_<MONTH>_<YEAR>
3 年多之前 回复
dt3999
dt3999 当您只需将注册日期添加到用户表时,为什么还有一个额外的表?这样做你只需要提取该日期在你的范围(月)内的数据
3 年多之前 回复
dongmaobeng7145
dongmaobeng7145 你能分享桌面结构吗?
3 年多之前 回复

4个回答

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
dongzangchui2072
dongzangchui2072 你在不同的表中找到相同的日期...你不觉得,它会减慢你的反应。 如果你想这样做,为什么不在几个月的基础上为不同的表使用简单的for循环(如果你的年份相同)。
3 年多之前 回复



如果将所有注册存储在单个表中,会不会更好? 它更容易查询和维护。</ p>

  //所选范围必须格式化
$ from ='2017-01-01 00:00:00'; \ n $ to ='2017-03-15 23:59:59';

//你必须在一个特定的表上只运行一次这个查询
$ query =“SELECT count(*)as number_of_signups FROM tablename WHERE created_on BETWEEN $ AND AND $ to“;
</ code> </ pre>

如果您希望每月在不同的表中存储注册,则可以查询每个 时间戳列上有数据范围的表。</ p>

  //初始化
$ count = 0;

//必须格式化所选范围
$ from ='2017-01-01 00:00:00';
$ to ='2017-03-15 23:59:59';

$ con = mysqli_connect(“yourhost”,“dbuser” ,“password”,“your_db”);
//检查连接
if(mysqli_connect_errno())
{
echo“无法连接到MySQL:”。 mysqli_connect_error();
}

//在所有注册表上运行此查询
$ query =“SELECT count(*)as number_of_signups FROM tablename WHERE created_on BETWEEN $ from AND $ to”;

$ result = mysqli_query($ con,$ query);

//关联数组
$ row = mysqli_fetch_assoc($ result);
$ count + = $ row ['number_of_signups'];

/ / free结果集
mysqli_free_result($ result);

mysqli_close($ con);
</ code> </ pre>

正如您所看到的,这部分变得非常冗长和复杂: / p>

  //在所有注册表上运行此查询
$ query =“SELECT count(*)as number_of_signups FROM tablename WHERE created_on BETWEEN $ from AND $ to”;

$ result = mysqli_query($ con,$ query);

//关联数组
$ row = mysqli_fetch_assoc($ result);
$ count + = $ row ['number_of_signups'];
< / code> </ pre>
</ div>

展开原文

原文

Wouldn't it be a better if you store all the sign ups in a single table? It's easier to query and maintain.

// the selected range will have to be formatted
$from = '2017-01-01 00:00:00';
$to = '2017-03-15 23:59:59';

// you will have to run this query just once on one particular table 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

That being said if you do prefer to store sign ups in different tables on a monthly basis you can query each table with a data range on the timestamp column.

// initialization
$count = 0;

// the selected range will have to be formatted
$from = '2017-01-01 00:00:00';
$to = '2017-03-15 23:59:59';

$con=mysqli_connect("yourhost","dbuser","password","your_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// run this query on all the signup tables 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

$result=mysqli_query($con,$query);

// Associative array
$row=mysqli_fetch_assoc($result);
$count += $row['number_of_signups'];

// Free result set
mysqli_free_result($result);

mysqli_close($con);

As you can see this part gets really lengthy and complicated:

// run this query on all the signup tables 
$query = "SELECT count(*) as number_of_signups FROM tablename WHERE created_on BETWEEN $from AND $to";

$result=mysqli_query($con,$query);

// Associative array
$row=mysqli_fetch_assoc($result);
$count += $row['number_of_signups'];

duanbipu1720
duanbipu1720 感谢Muralidhar Bg,但桌子不是单月其表我将如何做到这一点。
3 年多之前 回复



  $ sql =“SELECT id,country,device,
LOWER(MONTHNAME(tracking_date))AS月,
YEAR(tracking_date)AS
SUM(user_signup_count)ASminual_sum
FROM daily_analytics_01_2017
WHERE tracking_date BETWEEN'$ first'AND' $ last'
GROUP BY month“;
</ code> </ pre>

说明:
按月创建表并创建单个表并使用它是不是一个好主意。 以上查询就足够了。</ p>

  $ first  - 开始日期
$ last - 结束日期
</ code> </ pre>
</ div>

展开原文

原文

$sql = "SELECT  id, country,device,     
            LOWER(MONTHNAME(tracking_date)) AS month,
            YEAR(tracking_date) AS `year`,
            SUM(user_signup_count) AS `monthly_sum`
        FROM daily_analytics_01_2017
        WHERE tracking_date BETWEEN '$first' AND '$last'
        GROUP BY month";

Explanation: It is not a good idea to create table month wise, create a single table and use that. above query will be enough for that.

$first - start date
$last - end date

dou5454954610
dou5454954610 好的,会检查一下。 午饭时间到。
3 年多之前 回复
dongwenghe2416
dongwenghe2416 这是令人困惑的,你需要月份明智的数据,但你有月份表!
3 年多之前 回复



检查此代码,它将显示所有之前12个月的登录用户注册信息。 您需要将此代码更改为表字段和表名。</ p>

 
中选择date_year y,date_month m,count(*)cnt
(选择id,年份 (access_time)date_year,month(access_time)date_month,access_time,uid
来自users_login_detail_history
其中date(access_time)&gt; = date_format(concat(year(date_add(date(now()),interval -1 year)), ' - ',月(date_add(date(now()),interval -1 year)),' - 1'),'%Y-%m-%d'))login_data
group by date_year,date_month
按date_year,date_month排序;
</ code> </ pre>

从这个sql输出结果
</ p>
</ DIV>

展开原文

原文

check this code , it will show all previous 12 month login user signup information . you need to change this code as your table field and table name.

select date_year y, date_month m, count(*) cnt
            from
            (select id, year(access_time) date_year, month(access_time) date_month, access_time, uid
            from users_login_detail_history
            where date(access_time) >= date_format(concat (year(date_add(date(now()), interval -1 year)), '-' , month(date_add(date(now()), interval -1 year)) , '-1'), '%Y-%m-%d')) login_data
            group by date_year, date_month
            order by date_year, date_month;

From this sql this is output result enter image description here

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐