mybatis sql 如何分组查询成功和失败统计次数

本文详细解析了一段SQL查询代码,旨在统计指定日期(2020年1月18日)的成功与失败订单数量。通过对品牌ID和门店ID进行分组,展示了如何使用case when语句来区分订单状态,并计算每种状态的数量。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

需求,成功状态为1,失败状态为0,现在统计18号的成订单多少次,失败订单多少次?

select BRAND_ID,STORE_ID
,sum(case when ORDER_STATUS = 1  then 1 else 1 end) as 成功
,sum(case when ORDER_STATUS = 0  then 0 else 1 end) as 失败
from t_loreal_product_order t
where CREATE_TIME >='2020-01-18 00:00:00' and CREATE_TIME <='2020-01-18 23:59:59'
group by BRAND_ID,STORE_ID
<think>我们基于Ruoyi框架(一个基于Spring BootMyBatis的快速开发框架)来实现网站访问量点击率的统计功能。根据引用[1]中的描述,我们可以借鉴RuoYi-fast-cms项目的思路,但需要根据Ruoyi框架的特点进行调整。 主要思路: 1. 通过拦截器(Interceptor)或过滤器(Filter)来拦截请统计访问量(PV)独立访客(UV)。 2. 使用Redis来存储临时数据,以提高性能并方便统计(如引用[4]中提到的使用Redis的数据结构进行统计)。 3. 将统计结果定期或实时存入数据库。 步骤: 1. 创建数据库表来存储访问统计信息。 2. 编写拦截器,在每次请时进行统计。 3. 使用Redis记录独立访客(UV)页面访问量(PV),并考虑如何避免重复计数(如一天内同一用户多次访问只算一个UV)。 4. 将统计结果持久化到数据库。 详细实现: 一、数据库设计(参考引用[1]中的需分析) 我们可以设计一张表`sys_visit_log`,包含以下字段: - id: 主键 - ip: 访问者IP地址 - visit_date: 访问日期(如20210601) - pv: 该IP在该日期的访问次数(PV) - uv: 该IP在该日期是否已经计数过(UV用去重计数,所以这个字段不需要,但我们可以通过日期IP去重来统计UV) 但是,为了更灵活,我们可以设计两张表: 1. 访问记录表(记录每次访问,用于详细分析): - id: 主键 - ip: 访问者IP - url: 访问的URL - create_time: 访问时间 - user_agent: 浏览器标识(可选) - session_id: 会话ID(可选,用于识别同一用户) 2. 统计汇总表(按天汇总): - id: 主键 - visit_date: 日期(年月日) - pv: 当天的总PV - uv: 当天的总UV(独立访客数) 但是,由于数据量可能很大,我们可以直接使用Redis进行实时统计,然后每天将统计结果存入数据库。详细设计如下: 二、使用Redis统计PVUV 引用[4]中提到使用Redis的数据结构(如Set、HyperLogLog)进行统计。我们可以: - PV:使用Redis的INCR命令,按天为每个页面生成一个PV的key,如`pv:20210601:/index`。如果要统计全站的PV,可以用一个总的key,如`pv:20210601:total`。 - UV:使用HyperLogLog(适合大数据量且不要精确统计)或者Set(精确但占用内存大)来存储当天的独立IP。例如,使用HyperLogLog,key为`uv:20210601`,每次将访问的IP添加进去。 但是,我们还需要按天汇总,所以每天的数据用当天的日期作为key的一部分。 三、实现拦截器 1. 创建一个拦截器`VisitInterceptor`,实现`HandlerInterceptor`接口。 2. 在`preHandle`方法中,获取请的IP、当前日期(格式如yyyyMMdd)的URL。 3. 使用RedisTemplate操作Redis: - 增加PV:对当前日期的全站PV当前URL的PV分别增加1。 redisTemplate.opsForValue().increment("pv:" + today + ":total", 1); redisTemplate.opsForValue().increment("pv:" + today + ":" + url, 1); - 记录UV:使用HyperLogLog,将IP添加到当天的UV集合中。 redisTemplate.opsForHyperLogLog().add("uv:" + today, ip); 注意:UV的统计需要去重,HyperLogLog会自动去重。 四、持久化到数据库 我们可以每天凌晨将前一天的统计数据存入数据库。使用Spring Task定时任务。 1. 创建定时任务,每天0点10分执行。 2. 从Redis中读取前一天的PVUV数据(注意:前一天的日期可以用`LocalDate.now().minusDays(1)`)。 - 全站PV:key为`pv:yyyyMMdd:total` - 全站UV:通过`redisTemplate.opsForHyperLogLog().size("uv:yyyyMMdd")`获取UV数量 3. 将数据存入数据库的统计汇总表(如`sys_visit_summary`)。 五、数据库表设计(统计汇总表) 表名:sys_visit_summary 字段: id: 主键(自增) visit_date: 日期(date类型,如2021-06-01) pv: 整型,总访问量 uv: 整型,独立访客数 六、点击率统计 点击率通常是指某个特定链接(如广告、文章)被点击的次数。我们可以: 1. 在需要统计的链接上添加一个重定向,例如`/click?target=广告ID`,然后在这个重定向的控制器中统计点击次数。 2. 同样使用Redis来记录每个广告每天的点击次数,然后定时持久化到数据库。 七、代码实现(关键部分) 1. 拦截器代码示例: ```java @Component public class VisitInterceptor implements HandlerInterceptor { @Autowired private RedisTemplate<String, String> redisTemplate; @Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception { // 获取IP String ip = IpUtils.getIpAddr(request); // 获取当前日期,格式yyyyMMdd String today = DateUtils.formatDate(new Date(), "yyyyMMdd"); // 获取请的URL(不包括参数) String url = request.getRequestURI(); // 全站PV增加 redisTemplate.opsForValue().increment("pv:" + today + ":total", 1); // 当前URL的PV增加 redisTemplate.opsForValue().increment("pv:" + today + ":" + url, 1); // UV统计(使用HyperLogLog) redisTemplate.opsForHyperLogLog().add("uv:" + today, ip); return true; } } ``` 2. 注册拦截器(在配置类中): ```java @Configuration public class WebConfig implements WebMvcConfigurer { @Autowired private VisitInterceptor visitInterceptor; @Override public void addInterceptors(InterceptorRegistry registry) { registry.addInterceptor(visitInterceptor).addPathPatterns("/**"); } } ``` 3. 定时任务(每天凌晨处理前一天的统计): ```java @Component public class VisitStatisticTask { @Autowired private RedisTemplate<String, String> redisTemplate; @Autowired private SysVisitSummaryMapper visitSummaryMapper; // MyBatis Mapper @Scheduled(cron = "0 10 0 * * ?") // 每天0:10执行 public void execute() { // 获取前一天的日期 LocalDate yesterday = LocalDate.now().minusDays(1); String dateStr = yesterday.format(DateTimeFormatter.ofPattern("yyyyMMdd")); String dateDisplay = yesterday.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")); // 从Redis获取前一天的PV String totalPvKey = "pv:" + dateStr + ":total"; Long pv = Long.valueOf(0); String pvStr = redisTemplate.opsForValue().get(totalPvKey); if (pvStr != null) { pv = Long.parseLong(pvStr); } // 从Redis获取前一天的UV String uvKey = "uv:" + dateStr; Long uv = redisTemplate.opsForHyperLogLog().size(uvKey); // 存入数据库 SysVisitSummary summary = new SysVisitSummary(); summary.setVisitDate(java.sql.Date.valueOf(yesterday)); summary.setPv(pv); summary.setUv(uv); visitSummaryMapper.insert(summary); // 删除Redis中前天的数据(可选,根据需) redisTemplate.delete(totalPvKey); redisTemplate.delete(uvKey); // 注意:这里只删除了全站PVUV的key,还有各个URL的PV的key没有删除,可以根据需要清理 } } ``` 八、注意事项 1. IP获取:使用`IpUtils.getIpAddr(request)`(Ruoyi框架中有提供)。 2. 拦截器路径:我们拦截了所有请(`/**`),但也可以排除一些静态资源等。 3. HyperLogLog的误差:HyperLogLog在统计UV时会有约0.81%的误差,如果要精确,可以考虑使用Set,但内存占用会大很多。 4. 性能:使用Redis后,统计操作对性能影响很小。 九、点击率统计的实现 对于特定链接的点击,我们可以在拦截器中根据URL模式进行统计,但更常见的做法是为每个需要统计的链接生成一个带统计参数的跳转链接,然后通过一个控制器来跳转并统计。 例如,在页面上,一个广告的链接可能是: <a href="/click?adId=1">广告1</a> 然后,我们编写一个控制器: ```java @Controller public class ClickController { @Autowired private RedisTemplate<String, String> redisTemplate; @GetMapping("/click") public String click(@RequestParam("adId") Long adId, HttpServletRequest request, HttpServletResponse response) throws IOException { // 获取当前日期 String today = DateUtils.formatDate(new Date(), "yyyyMMdd"); // 统计这个广告今天的点击量 redisTemplate.opsForValue().increment("click:" + today + ":ad:" + adId, 1); // 这里应该根据adId获取广告的真实URL,然后重定向 String realUrl = ...; // 从数据库或配置中获取 response.sendRedirect(realUrl); return null; } } ``` 同样,我们可以在定时任务中把广告点击量持久化到数据库。 这样,我们就实现了在Ruoyi框架中统计网站访问量点击率的功能。 引用说明: [^1]: 参考了RuoYi-fast-cms项目的网站统计分析。 [^2]: 引用了UV响应时间的定义。 [^3]: 参考了限流规则的设计,但本实现中未涉及限流。 [^4]: 参考了使用Redis的HyperLogLog进行数据统计的方法。 相关问题: 1. 如何避免在Ruoyi框架中重复统计同一用户的访问? 2. 除了使用HyperLogLog,还有哪些方法可以统计UV? 3. 如何实现按小时或按分钟的实时访问统计?</think>基于Ruoyi框架实现网站访问量点击率统计功能,可参考以下方案: ### 一、核心实现方案 1. **创建统计拦截器**(核心组件) ```java @Component public class VisitInterceptor implements HandlerInterceptor { @Autowired private RedisTemplate<String, String> redisTemplate; @Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) { String ip = IpUtils.getIpAddr(request); // 获取客户端IP String uri = request.getRequestURI(); // 访问路径 String today = LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE); // 当日日期 // PV统计(全站访问量) redisTemplate.opsForValue().increment("site:pv:total", 1); redisTemplate.opsForValue().increment("site:pv:" + today, 1); // UV统计(独立访客)[^4] redisTemplate.opsForHyperLogLog().add("site:uv:" + today, ip); // 页面点击统计 redisTemplate.opsForHash().increment("page:clicks", uri, 1); return true; } } ``` 2. **注册拦截器**(`WebConfig.java`) ```java @Configuration public class WebConfig implements WebMvcConfigurer { @Autowired private VisitInterceptor visitInterceptor; @Override public void addInterceptors(InterceptorRegistry registry) { registry.addInterceptor(visitInterceptor) .addPathPatterns("/**") .excludePathPatterns("/css/**", "/js/**"); // 排除静态资源 } } ``` 3. **数据库设计**(MySQL) ```sql CREATE TABLE sys_visit_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, visit_date DATE NOT NULL COMMENT '访问日期', pv_count INT DEFAULT 0 COMMENT '页面浏览量', uv_count INT DEFAULT 0 COMMENT '独立访客数', UNIQUE KEY uniq_date (visit_date) ); CREATE TABLE sys_page_click ( id BIGINT PRIMARY KEY AUTO_INCREMENT, page_url VARCHAR(255) NOT NULL COMMENT '页面路径', click_count INT DEFAULT 0 COMMENT '点击次数', log_date DATE NOT NULL COMMENT '记录日期' ); ``` 4. **定时数据持久化**(Spring Task) ```java @Scheduled(cron = "0 0 2 * * ?") // 每天凌晨2点执行 public void saveDailyStats() { String today = LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE); // 获取PV数据 Long pv = redisTemplate.opsForValue().get("site:pv:" + today); // 获取UV数据(HyperLogLog)[^4] Long uv = redisTemplate.opsForHyperLogLog().size("site:uv:" + today); // 保存到数据库 VisitLog log = new VisitLog(); log.setVisitDate(LocalDate.now()); log.setPvCount(pv != null ? pv.intValue() : 0); log.setUvCount(uv != null ? uv.intValue() : 0); visitLogMapper.insert(log); // 清空前一日Redis数据 redisTemplate.delete("site:uv:" + yesterday); } ``` ### 二、关键功能实现 1. **IP去重处理**(UV统计) - 使用Redis的HyperLogLog结构,内存占用仅需12KB/百万UV[^4] - 误差率约0.81%,满足一般统计 2. **高性能设计** ```mermaid graph LR A[用户请] --> B[拦截器] B --> C{Redis操作} C --> D[INCR PV计数器] C --> E[PFADD UV去重] C --> F[HINCRBY 页面点击] D --> G[异步持久化] ``` 3. **点击率计算**(API示例) ```java @GetMapping("/stats/clickRate") public AjaxResult getClickRate(@RequestParam String pageUrl) { // 获取页面点击量 Long clicks = redisTemplate.opsForHash().get("page:clicks", pageUrl); // 获取该页面PV作为分母 Long pagePv = redisTemplate.opsForValue().get("page:pv:" + pageUrl); // 计算点击率 = 点击量 / 页面访问量 BigDecimal rate = BigDecimal.valueOf(clicks) .divide(BigDecimal.valueOf(pagePv), 4, RoundingMode.HALF_UP); return AjaxResult.success(rate.multiply(BigDecimal.valueOf(100)) + "%"); } ``` ### 三、增强功能建议 1. **实时看板**: ```java @GetMapping("/dashboard") public AjaxResult getRealTimeStats() { Map<String, Object> map = new HashMap<>(); map.put("todayPV", redisTemplate.opsForValue().get("site:pv:" + today)); map.put("todayUV", redisTemplate.opsForHyperLogLog().size("site:uv:" + today)); map.put("topPages", redisTemplate.opsForZSet().reverseRange("page:ranking", 0, 5)); return AjaxResult.success(map); } ``` 2. **限流防护**(防刷机制)[^3] ```java // 在拦截器中添加限流 if(redisTemplate.opsForValue().get("ip_limit:"+ip) > 100) { response.sendError(429, "访问过于频繁"); return false; } redisTemplate.opsForValue().increment("ip_limit:"+ip, 1); redisTemplate.expire("ip_limit:"+ip, 1, TimeUnit.MINUTES); ``` ### 四、部署注意事项 1. **Redis配置**(`application.yml`) ```yaml spring: redis: host: 127.0.0.1 port: 6379 database: 0 timeout: 3000 ``` 2. **数据一致性**: - 使用Redis事务保证PV/UV操作的原子性 - 添加异常重试机制防止持久化失败 > **实现效果**:系统将自动记录`全站PV/UV`、`页面级点击量`,通过`sys_visit_log`表可查询历史趋势,通过Redis可获取实时数据。前端可通过`/stats`接口获取JSON数据生成可视化图表[^1][^4]。 --- ### 相关问题 1. 如何在高并发场景下保证统计数据的准确性? 2. 除了HyperLogLog,还有哪些Redis数据结构适合做UV统计? 3. 如何实现按省份分布的访问量统计? 4. 在Ruoyi框架中如何集成ECharts展示统计报表? [^1]: 网站统计的核心价值在于理解用户行为优化运营策略 [^2]: UV(独立访客)是衡量真实用户规模的关键指标 [^3]: 多维度限流策略可保护统计系统不被异常流量冲击 [^4]: Redis的HyperLogLog是实现海量UV统计的高效方案
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值