在SQL查询之间获取多个日期范围并相应地添加数据

Question

ran into a bit of an issue today.

Basically, I need to select a certain range of dates after I have already completed the query SELECT * FROM <DB>.

For example:

var (date string
     views int
     impressions int)

for query.Next() { 
    err := query.Scan(&date, &views, &impressions)
    // handle the err
    // get the range of dates for each month
    // add up all the views and impressions in that specific range
}

The 'date' var will obviously be all of the dates in the database query.

Dates are formatted as: 2017-10-01 (October 1st as an example) and there are about 300 in October and 100 in November.

Basically from here, I need to add up all the values (views and impressions), but only per date range.

So I would get something like:

2017-10-01 to 2017-10-31 has 54 impressions
2017-10-01 to 2017-10-07 has 5 impressions as an example.

Any idea how I'd come about this issue?
Hope I explained this alright and thanks in advanced.

Answer 1

So, the best bet in these cases is to use a map-style strategy to keep track. For example, a map[date]data would allow you to keep a unique entry for each date. Dates, however, have a beneficial optimization, in that they can easily be represented by integers (the day of the year), and the number of options is small enough to not be a memory issue. This means we can use a slice instead of a map and get the benefits of ordering (Go maps are randomly ordered in a for loop) while still using it like a map. For example:

type Data struct {
    // fields
}

const dateFormat = "2006-01-02" // only parse the date

dayStats := make([]Data, 366) // account for leap years

for query.Next() {
    var datestr string // can make this a time.Time, if your date format scans properly
    var dr Data
    if err := query.Scan(datestr, /* other fields */ ); err != nil {
        log.Fatal(err)
    }

    date, err := time.Parse(datestr)
    if err != nil {
        log.Fatal(err)
    }

    dayStats[date.YearDay()].someField += dr.someField
    dayStats[date.YearDay()].someOtherField += dr.someOtherField
    // other fields...
}

Now let's say we want to calculate the stats between a 01 October and 31 October:

start := time.Date(2017, time.October, 1, 0, 0, 0, 0, time.UTC)
end := time.Date(2017, time.October, 31, 0, 0, 0, 0, time.UTC)

var total Data
for day := start.YearDay(); day <= end.YearDay(); day++ {
    total.someField += dayStats[day].someField
    total.someOtherField += dayStats[day].someOtherField
    // other fields...
}