sql - 根据日期计算交易量
问题描述
我有这张 MariaDB 表,我想将其用于条形图:
CREATE TABLE `payment_transaction_daily_facts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`year` int(11) DEFAULT NULL,
`month` int(11) DEFAULT NULL,
`week` int(11) DEFAULT NULL,
`day` int(11) DEFAULT NULL,
`volume` int(11) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
'created_at' date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
在我的示例 SQL 查询中,我有一个日期列。当我将日期、年、月、周和日拆分为不同的列时,如何计算过去 10 天的每日交易量?
最终结果应该是例如:
Date | Amount| Number of transactions per day |
11-11-2018 | 30 | 3 |
11-12-2018 | 230 | 13 |
我试过这个:
SELECT SUM(amount) AS sum_volume, COUNT(*) AS sum_Transactions
WHERE (created_at BETWEEN '2018-11-07' AND '2018-11-08')
GROUP BY DATE(created_at)
我想使用 DTO 返回生成的数据:
public class DashboardDTO {
private Date date;
private int sum_volume;
private int sum_Transactions;
... getters and setters
}
休息控制器:
@RestController
@RequestMapping("/dashboard")
public class DashboardController {
private static final Logger LOG = LoggerFactory.getLogger(DashboardController.class);
@Autowired
private DashboardRepository dashboardRepository;
@Autowired
private PaymentTransactionsDailyFactsMapper mapper;
@GetMapping("/volumes")
public ResponseEntity<List<DashboardDTO>> getProcessingVolumes(@PathVariable String start_date, @PathVariable String end_date) {
List<DashboardDTO> list = StreamSupport.stream(dashboardRepository.findPaymentTransactionsDailyFacts(start_date, end_date).spliterator(), false)
.map(mapper::toDTO)
.collect(Collectors.toList());
return ResponseEntity.ok(list);
}
}
JPA 查询:
public List<PaymentTransactionsDailyFacts> findPaymentTransactionsDailyFacts(LocalDateTime start_date, LocalDateTime end_date) {
String hql = "SELECT SUM(amount) AS sum_volume, COUNT(*) AS sum_Transactions " +
" WHERE (created_at BETWEEN :start_date AND :end_date )" +
" GROUP BY DATE(created_at)";
TypedQuery<PaymentTransactionsDailyFacts> query = entityManager.createQuery(hql,
PaymentTransactionsDailyFacts.class).setParameter("start_date", start_date).setParameter("end_date", end_date);
List<PaymentTransactionsDailyFacts> data = query.getResultList();
return data;
}
我应该如何正确实施查询?
当我从 Angular 收到 start_date 和 end_date 作为字符串时,我应该如何将其转换为 LocaDateTime?
解决方案
好吧,正如我所评论的,时间是数据仓库星型模式中的一个维度,我想周期也是如此。因此,您应该有两个维度表,一个TimeDim
用于 LocalDate,一个PeriodDim
用于 Period。然后,您应该拥有一个Fact
由架构中的各个维度组成的 EmbeddedId。然后,您将获得 1 天期间的事实和 10 天期间的事实。如果您坚持总结事实,您就会遇到 JPA 无法与复合键进行比较<=
的问题。>=
由于您只计算 10 天的总和,您可以使用一个in
子句来选择 10 个键,但同样,您应该有您需要的时间段的事实。
@Entity
public class TimeDim {
@Id
private LocalDate localDate;
@Entity
public class PeriodDim {
@Id
private Period period;
// need this too
@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {
@Override
public Date convertToDatabaseColumn(LocalDate locDate) {
return (locDate == null ? null : Date.valueOf(locDate));
}
@Override
public LocalDate convertToEntityAttribute(Date sqlDate) {
return (sqlDate == null ? null : sqlDate.toLocalDate());
}
}
@SuppressWarnings("serial")
@Embeddable
public class DimKey implements Serializable {
private LocalDate localDate;
private Period period;
@Entity
public class Fact {
@EmbeddedId
private DimKey dimKey = new DimKey();
private long amount;
例如:
tx.begin();
TimeDim td10 = new TimeDim();
td10.setLocalDate(LocalDate.now().minusDays(5));
em.persist(td10);
TimeDim td5 = new TimeDim();
td5.setLocalDate(LocalDate.now().minusDays(10));
em.persist(td5);
PeriodDim pd5 = new PeriodDim();
pd5.setPeriod(Period.ofDays(5));
em.persist(pd5);
PeriodDim pd10 = new PeriodDim();
pd10.setPeriod(Period.ofDays(10));
em.persist(pd10);
Fact f10 = new Fact();
f10.getDimKey().setLocalDate(td10.getLocalDate());
f10.getDimKey().setPeriod(pd10.getPeriod());
f10.setAmount(100);
em.persist(f10);
Fact f51 = new Fact();
f51.getDimKey().setLocalDate(td10.getLocalDate());
f51.getDimKey().setPeriod(pd5.getPeriod());
f51.setAmount(50);
em.persist(f51);
Fact f52 = new Fact();
f52.getDimKey().setLocalDate(td5.getLocalDate());
f52.getDimKey().setPeriod(pd5.getPeriod());
f52.setAmount(50);
em.persist(f52);
tx.commit();
em.clear();
DimKey dk = new DimKey();
dk.setLocalDate(td10.getLocalDate());
dk.setPeriod(pd10.getPeriod());
Fact f = em.createQuery("select f from Fact f where f.dimKey = :dimKey", Fact.class)
.setParameter("dimKey", dk)
.getSingleResult();
System.out.println("From 10 day period: " + f.getAmount());
DimKey dk1 = new DimKey();
dk1.setLocalDate(td10.getLocalDate());
dk1.setPeriod(pd5.getPeriod());
DimKey dk2 = new DimKey();
dk2.setLocalDate(td5.getLocalDate());
dk2.setPeriod(pd5.getPeriod());
Long sum = em.createQuery("select sum(f.amount) from Fact f where f.dimKey in (:dimKey1 , :dimKey2)", Long.class)
.setParameter("dimKey1", dk1)
.setParameter("dimKey2", dk2)
.getSingleResult();
System.out.println("From 2*5 day period: " + sum);
推荐阅读
- python - 当我将 WholeTextFiles() 与 pyspark 一起使用时,为什么 AWS 拒绝我的连接?
- html - Beautiful Soup 无法提取链接
- google-apps-script - 如何强制 Google Visualization Table 使用我的标题行?
- scala - Scala的延迟初始化?
- raspberry-pi - 您需要购买什么来与 ZigBee 和 Z-Wave 设备通信?
- javascript - 如何使用正确的角度路由在角度的新选项卡中打开 mat-menu-item?
- javascript - Node.js 中的“url.searchParams”和“URLSearchParams”有什么区别?
- java - FXML ListView,我无法添加我的 ObservableArrayList
对它 - delphi - Delphi:大文件(100 MB)的快速流到 sha256
- javascript - Javascript是否有一种“切片”地图的好方法