dorisdong0514 2017-11-07 15:26
浏览 102
已采纳

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

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.

  • 写回答

1条回答 默认 最新

  • drtiwd06558 2017-11-07 20:02
    关注

    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...
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题