dev/常用SQL语句/dcp对接文档/(双周报)访问人数查询SQL.txt

/**登录明细列表合计数**/
        

          SELECT uu.id,uu.user_uid,uu.user_email,uu.user_preferred_mobile,country
             ,(SELECT MAX(bue.unit_name) FROM base_unit_entity bue WHERE bue.id IN(SELECT uuu.unit_id FROM unit_ums_user uuu WHERE uuu.ums_id=uu.id)) AS unit_name
             ,(SELECT MAX(bue.are) FROM base_unit_entity bue WHERE bue.id IN(SELECT uuu.unit_id FROM unit_ums_user uuu WHERE uuu.ums_id=uu.id)) AS unit_name
             ,COUNT(DISTINCT DATE_FORMAT(sl.create_date,'%Y-%m-%d')) AS logincount
             ,
            MAX(DATE_FORMAT(sl.create_date,'%Y-%m-%d')) AS login_date,COUNT(DISTINCT DATE_FORMAT(sl.create_date,'%Y-%m-%d')) AS logincount
        FROM
            sys_log_extend sl,ums_user uu
        WHERE
            sl.create_by = uu.id
          AND sl.create_date >= "2021-08-16" AND sl.create_date <"2021-08-29"
        GROUP BY uu.id,uu.user_uid,uu.user_email,uu.user_preferred_mobile


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/**国内访问总数**/（记下来，不填充）

 	SELECT COUNT(1) AS '访问数量' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND country = "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";




/**国外访问总数**/

 	SELECT COUNT(1) AS '访问数量' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND country  != "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/**国内已注册用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND create_by !="0"
              AND country = "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";



/**国外已注册用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND create_by != "0"
              AND country != "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";



/**国内电脑端用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND device_type = "COMPUTER"
              AND country = "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";



/**国外电脑端用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND device_type = "COMPUTER"
              AND country != "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";



/**国内手机端用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND device_type = "MOBILE"
              AND country = "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";



/**国外手机端用户访问数**/

 	SELECT COUNT(1) AS '访问日期内' FROM sys_log_extend
        		WHERE (
                		request_uri LIKE    '/baseProjectViews%'
                		OR request_uri LIKE '/baseCountryViews%'
                		OR request_uri LIKE '/baseUnitViews%'
                		OR request_uri LIKE '/baseMemberInfos%'
                		OR request_uri LIKE '/committeeInformationReleaseManagementInfos%'
                		OR request_uri LIKE '/baseResearchResultViews%'
                		OR request_uri LIKE '/cmsInformationViews%'
                		OR request_uri LIKE '/meetingBasicInfos%'
               			OR request_uri LIKE '/activityInfos%'
               			OR request_uri LIKE '/baseFinancialInstit%'
                		OR request_uri LIKE '/baseThinkTankInfos%'
            			)
              AND device_type = "MOBILE"
              AND country != "中国"
              AND create_date  >= "2021-08-16" AND create_date  <"2021-08-30";