import com.xxx.repository.kpi.entity.ChannelCheckBase; import com.xxx.repository.kpi.entity.DataJson; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.mongodb.core.MongoTemplate; import org.springframework.data.mongodb.core.ScriptOperations; import org.springframework.data.mongodb.core.aggregation.Aggregation; import org.springframework.data.mongodb.core.aggregation.AggregationResults; import org.springframework.data.mongodb.core.query.Criteria; import org.springframework.data.mongodb.core.script.ExecutableMongoScript; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Service; import java.util.Date; /** * @autor wangy * @date 2021/3/16 13:33 **/ @Service public class ChannelResCountStatisticsService { private static final Logger logger = LoggerFactory.getLogger(ChannelResCountStatisticsService.class); @Autowired private MongoTemplate mongoTemplate; /** * 根据mongo中的目录ID、发布时间,统计每天、每个栏目下的资源量 * @return */ @Scheduled(cron = "0 0/1 * * * ?") public DataJson queryChannelResCountStatisticsByDay(){ System.out.println("new date()===>" + new Date()); DataJson dataJson = new DataJson(); try { ScriptOperations scriptOps = mongoTemplate.scriptOps(); String sqlStr = "db.getCollection(\"REPO_MANAGE_INFO\").aggregate(\n" + " {\n" + " $match: \n" + " {\n" + " 'st': '3',\n" + " \"$and\": [{\n" + " \"metd.Pubtime\": {\n" + " \"$gte\": \"2021-02-19 00:00:00\"\n" + " }\n" + " }, {\n" + " \"metd.Pubtime\": {\n" + " \"$lte\": \"2021-02-20 23:59:59\"\n" + " }\n" + " }]\n" + " }\n" + " }\n" + " ,\n" + " {\n" + " $group: \n" + " {\n" + " \"_id\": {\n" + " dir: \"$dir\",\n" + " Year: {\n" + " $substr: [\"$metd.Pubtime\", 0, 4]\n" + " },\n" + " Month: {\n" + " $substr: [\"$metd.Pubtime\", 5, 2]\n" + " },\n" + " Day: {\n" + " $substr: [\"$metd.Pubtime\", 8, 2]\n" + " }\n" + " },\n" + " \"orderNum\": {\n" + " \"$sum\": 1\n" + " }\n" + " }\n" + " }\n" + ");"; ExecutableMongoScript echoScript = new ExecutableMongoScript(sqlStr); // 执行mongo语句,并得到查询结果 Object directly_execute_script = scriptOps.execute(echoScript, "directly execute script"); if (1==1) { dataJson.setMsg("数据保存成功!"); dataJson.setStatus("0"); } else { dataJson.setMsg("数据保存失败!"); dataJson.setStatus("1"); } return dataJson; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); dataJson.setMsg(e.getMessage()); dataJson.setStatus("1"); return dataJson; } } }
import com.xxx.repository.kpi.entity.ChannelCheckBase; import com.xxx.repository.kpi.entity.DataJson; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.mongodb.core.MongoTemplate; import org.springframework.data.mongodb.core.ScriptOperations; import org.springframework.data.mongodb.core.aggregation.Aggregation; import org.springframework.data.mongodb.core.aggregation.AggregationResults; import org.springframework.data.mongodb.core.query.Criteria; import org.springframework.data.mongodb.core.script.ExecutableMongoScript; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Service; import java.util.Date; /** * @autor wangy * @date 2021/3/16 13:33 **/ @Service public class ChannelResCountStatisticsService { private static final Logger logger = LoggerFactory.getLogger(ChannelResCountStatisticsService.class); @Autowired private MongoTemplate mongoTemplate; /** * 根据mongo中的目录ID、发布时间,统计每天、每个栏目下的资源量 * @return */ @Scheduled(cron = "0 0/1 * * * ?") public DataJson queryChannelResCountStatisticsByDay(){ System.out.println("new date()===>" + new Date()); DataJson dataJson = new DataJson(); try { Criteria criteria = Criteria.where("st").is("3"); criteria.andOperator(Criteria.where("metd.Pubtime").lte("2021-02-20 23:59:59"), Criteria.where("metd.Pubtime").gte("2021-02-19 00:00:00")); Aggregation customerAgg = Aggregation.newAggregation( Aggregation.match(criteria), Aggregation.project("dir","year","month","day") .andExpression("{ dir: '$dir'," + " year: { " + " $substr: {'$metd.Pubtime', 0, 4}" + " }," + " Month: {" + " $substr: {'$metd.Pubtime', 5, 2}" + " }," + " Day: {" + " $substr: {'$metd.Pubtime', 8, 2}" + " } " + "}").as("_id"), Aggregation.group("_id").count().as("updateNum") ); // 进行查询,并得到查询结果 AggregationResults<ChannelCheckBase> aggregate = mongoTemplate.aggregate(customerAgg, "REPO_MANAGE_INFO", ChannelCheckBase.class); aggregate.getMappedResults(); if (1==1) { dataJson.setMsg("数据保存成功!"); dataJson.setStatus("0"); } else { dataJson.setMsg("数据保存失败!"); dataJson.setStatus("1"); } return dataJson; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); dataJson.setMsg(e.getMessage()); dataJson.setStatus("1"); return dataJson; } } }
//增加8小时时区
//{$dayOfWeek : { $add : [$createTime, 28800000]} }
project().andExpression("dayOfWeek(add(createTime," + 8 * 60 * 60 * 1000 + "))").as("day")
project().andExpression("dayOfWeek(add(createTime, [0]))", 8 * 60 * 60 * 1000).as("day") //等效于上条
//增加8小时时区
//{ "$dateToString" : { "date" : "$ctime", "format" : "%Y-%m-%d %H:%M:%S", "timezone" : "+08:00" } }}
project().andExpression("{ $dateToString: {date: '$createTime',
format: '%Y-%m-%d %H:%M:%S', timezone: '+08:00'}}").as("day")
//存在数组时的写法 {val1, val2}
//{ "$dateToString" : { "date" : { "$add" : ["$ctime", 28800000] }, "format" : "%Y-%m-%d %H:%M:%S" } } }
project().andExpression("{ $dateToString: {date: { $add: {'$ctime', [0]} }, format: '%Y-%m-%d %H:%M:%S'}}", 28800000).as("day")
project().andExpression("{ $dateToString: {date: { $add: {'$ctime', 28800000} }, format: '%Y-%m-%d %H:%M:%S'}}").as("day")
//等效于上条