Wsrtjtyk

I have this MariaDB table which I would like to use for bar chart:

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;

In my example SQL query I have single column for Date. How I can calculate the volumes per day for last 10 days when I have split date, year, month, week and day into different columns?

The final result should be for example:

Date       | Amount| Number of transactions per day |

11-11-2018 | 30    | 3                              |

11-12-2018 | 230   | 13                             |

I tried this:

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)

I want to return the generated data using DTO:

public class DashboardDTO {



    private Date date;



    private int sum_volume;



    private int sum_Transactions;



    ... getters and setters

}

Rest controller:

@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 query:

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;

    }

How should I implement the query properly?

When I receive start_date and end_date as String from Angular how should I convert it into LocaDateTime?

Well, as I commented, time is a dimension in a data warehouse star schema, and I guess period is as well. So you should have two dimension tables, a TimeDim for LocalDate, and a PeriodDim for Period. Then you should have a Fact with the an embeddedId made up of the various dimensions in your schema. Then you would have facts for 1 day periods and facts for 10 day periods. If you insisted on summing facts you have the issue that JPA cannot do a <= or >= comparison against composite keys. Since you are only summing 10 days you could use a in clause to select 10 keys, but again, you should have facts for the periods you need.

@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;

And for example:

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);