java - 查找时间范围内任何时间平均活跃的工人数量

Question

TLDR：希望使用下面的 SQL 表在一个时间范围内获取每个不同组的平均工人数（在任何时间）。通过使用 SQL 查询或将原始数据从 SQL 移动到 java 并在那里计算。

我正在尝试使用下表找到特定时间范围内（比如说 8/5/2019-9/4/2019）内任何时间可用的一组（每个组）的平均工人数量：

CREATE TABLE workers(
  group VARCHAR(75) NOT NULL,
  worker_name VARCHAR(75) NOT NULL,
  times tstzrange NOT NULL, 
); 

为了帮助澄清该表的工作原理：它旨在表示属于特定工人组的工人机器。我知道当时间字段上有一个 tstzrange 的结束界限时，工人已经离开了小组，否则如果工人仍在小组中，时间是无限的。工作人员可以在任何时间段内处于活动状态，并且“时间”可以跨越数天。仅当重新启动工作人员或添加全新的工作人员时才会添加新行。工作人员可以在几天之间保持活动状态，并且每天都不会创建新行。

使这变得更加困难的一个问题是，工人可能会在一天的整个过程中加入或加入团队，有时会在一个小时或更短的时间内加入。例如，一个时间范围内可能有 100-120 个工作人员行，即使该时间范围内任何时间的平均工作人员数量（我正在寻找）应该是 4。此外，我想要每个不同组的平均值. 目前我正在使用下面的查询来获取数据，将其映射到 Java 中的对象，然后从那里构造一个解决方案。

select worker_name, lower(times) as start, upper(times) as end, group
from workers
where times && tstzrange('2019-08-05', '2019-09-04')

大部分工作是在 Java 中完成的，我正在制作每个组的地图 -> 属于该组的所有工作人员及其时间戳。然后，我通过查看在此期间每天有多少工作人员处于活动状态，然后在整个时间范围内对其进行平均，以找到该时间段内每个组中可用的平均工作人员数量，从而合并此列表。似乎这有点低效，并且没有按预期对所有组工作，我想知道我是否能够直接在 SQL 中（或在 java 中有效）以更好的方式做到这一点。我一直在研究这个问题一段时间，并认为它一开始看起来很简单，但我正在努力想出一个更好的解决方案。希望有更多经验的人可以建议我解决这个问题的好方法，

这就是我在将数据从 Java 中的 SQL 获取到对象时使用的映射数据

public class WorkerMapping {

  private String group;
  private List<LocalDate> dates;

  public WorkerMapping(OffsetDateTime start, OffSetDateTime end, String group) {
    this.dates = start.toLocalDate().datesUntil(end.toLocalDate()).collect(Collectors.toList());
    this.group = group; 
  }

  // Getters and Setters
}

这就是我用作 HashMap 的键。这使得每个键和它活跃的日期成为地图中的一个键，这有助于总结每个组每天的出现情况。

public class DateGroupKey {
  private String group;
  private LocalDate date;

  public DateGroupKey(String group, LocalDate date) {
      this.group = group;
      this.date = date; 
  }

  // Override equals and hash to be used as key for HashMap

}

获取每组平均值的当前逻辑：

    public HashMap<String, Double> getAverage(List<WorkerMapping> rows)
    {
        HashMap<String, Double> workerAverage = new HashMap<>();
        // Treemap is used so keys can be processed in order, and all groups are together. 
        TreeMap<DateGroupKey, Integer> map = new TreeMap<>((a, b) -> a.getGroup().equals(b.getGroup()) ?
                a.getDate().compareTo(b.getDate()) : a.getGroup().compareTo(b.getGroup()));
        for (HostsRow row : rows)
        {
            for (LocalDate date : row.getDates()) {
                DateGroupKey key = new DateGroupKey(row.getGroup(), date);
                if (map.containsKey(key)) {
                    map.put(key, map.get(key) + 1);
                } else {
                    map.put(key, 1);
                }
            }
        }

        List<DateGroupKey> keys = new ArrayList<>(map.keySet());
        String currentGroup = keys.get(0).getGroup();
        int currentSum = 0;

        for (DateGroupKey key : keys ) {
            if (!key.getGroup().equals(currentGroup)) {
                workerAverage.put(currentGroup, Math.ceil(currentSum / 30.0));
                currentGroup = key.getGroup();
                currentSum = 0;
            }
            currentSum += map.get(key);
        }
        workerAverage.put(currentGroup, Math.ceil(currentSum / 30.0));
        return workerAverage;
    }

我希望通过使用更好的 sql 查询或更高效的 java 在时间范围内的任何时间接收组到普通工作人员的映射。

标签： javasqlalgorithmdatetimedate-range