使用数据库中的标准差构建图表显示平均值的分布情况

在Golang实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《使用数据库中的标准差构建图表显示平均值的分布情况》，聊聊，希望可以帮助到正在努力赚钱的你。

解释起来很棘手，所以我会将信息缩小到最少：

但首先，我将尝试解释我的最终目标，我想让试用产品的用户确定该产品如何影响一个值（与他们的平均基线相比），然后将所有这些百分比与标准开发人员平均.

我有一个数据库，其中有一个包含 user_id、值和日期的表。

然后，我有第二个表，其中指示用户的试用开始和结束的时间以及他们用于所述试用的产品。

我想要做的是收集一种产品类型的所有用户试用，并为每个参与的用户获取基线值及其每天的变化百分比。然后对所有这些百分比进行平均，得到每天的标准差。

1. 一个问题是日期需要转换为自 start_date 以来的天数，因此开始日期和前 24 小时之间的任何内容都将集中为第 0 天，接下来的 24 小时将集中为第 1 天，依此类推。所以我会平均每天的百分比

2. 并非每个用户的每一天都会被记录，因此有些用户可能会有多个缺失的日子，所以我不需要将每一天标记为从开始​​算起的天数

3. 开始日期在用户之间是随机的

所以图表将如下所示：

图片

我更愿意尽可能多地用 sql 来做，但其余的将用 golang 来做。

我就想着抓住每一次的尝试，然后每次的尝试都会有一系列的结果。因此，然后我迭代每个试验，并迭代每个试验的结果，选择第 0 天、第 1 天、第 2 天，并将它们保存在自己的数组中，然后我将对其进行平均。但一切都开始变得超级混乱

例如半伪代码：

db.query("select user_id, start_date from trials where product_id = $1", productid).scan(&trial.userid, &trial.startdate)

//extract trials from rows

for _, trial := range trials {
    // extract leadingavgstart from startdate
  db.queryrow("select avg(value) from results where user_id = $1 date between $2 and $3", trial.userid, leadingavgstart, trial.startdate)
    // now we have the baseline for the user

  rows :=  db.query("select value, date from results where product_id = $1", start)
   //now we extract the results and have and array
   //convert dates to dates from start date
   //...? it just start getting ugly and i believe there has to be a better way
}

如何使用 sql 完成大部分繁重的工作？

create table users (id int primary key, name text);
create table products (id int primary key, name text);

create table values (
      id int primary key
    , user_id int references users(id)
    , value int
    , date numeric
);

create table trials (
      id int primary key
    , user_id int references users(id)
    , start_date numeric
    , end_date numeric
    , product_id int references products(id)
);

insert into users (id, name ) values
(1,'john'),
(2,'jane'),
(3,'billy'),
(4,'miranda');

insert into products (id, name ) values
(1, 'pill a'),
(2, 'pill b'),
(3, 'pill c'),
(4, 'exercise bal'),
(5, 'diet plan');

insert into trials (id,user_id,start_date,end_date,product_id) values
(1, 1, 1667896408000, 1668099442000, 1),
(2, 1, 1667896408000, 1668099442000, 2),
(3, 2, 1667576960000, 1668074401000, 3),
(4, 3, 1667896408000, 1668099442000, 1);


insert into values (id, user_id, value, date) values
(38, 1, 7, 1668182428000),
(1, 1, 7, 1668099442000),
(2, 1, 8, 1668074401000),
(3, 1, 8, 1668012300000),
(4, 1, 6, 1668011197000),
(5, 1, 6, 1667978268000),
(6, 1, 9, 1667925002000),
(7, 1, 9, 1667896408000),
(8, 1, 4, 1667838601000),
(9, 1, 6, 1667803049000),
(10, 1, 7, 1667576960000),
(12, 1, 5, 1667546428000),
(13, 1, 8, 1667490149000),
(14, 2, 8, 1668182428000),
(15, 2, 7, 1668099442000),
(16, 2, 8, 1668074401000),
(17, 2, 9, 1668012300000),
(18, 2, 6, 1668011197000),
(19, 2, 6, 1667978268000),
(20, 2, 5, 1667925002000),
(21, 2, 9, 1667896408000),
(22, 2, 4, 1667803049000),
(23, 2, 4, 1667576960000),
(24, 2, 5, 1667546428000),
(25, 2, 9, 1667490149000),
(26, 3, 6, 1668182428000),
(27, 3, 7, 1668099442000),
(28, 3, 8, 1668074401000),
(29, 3, 9, 1668011197000),
(30, 3, 6, 1667978268000),
(31, 3, 9, 1667925002000),
(32, 3, 9, 1667896408000),
(33, 3, 8, 1667838601000),
(34, 3, 6, 1667803049000),
(35, 3, 4, 1667576960000),
(36, 3, 5, 1667546428000),
(37, 3, 6, 1667490149000);

正确答案

好吧，我明白了，基本上我做了两个内部联接查询，并将它们视为表，然后内部联接它们，并使用分组来求平均值

select 
  query1.product_uuid, 
  query1.days, 
  AVG(query1.value / query2.avg) as avg_percent 
from 
  (
    select 
      DATE_PART(
        'day', 
        to_timestamp(
          values 
            .date / 1000
        ):: date - trials.start_date
      ) as days, 
      trials.uuid as trial_uuid, 
      trials.product_uuid, 
    values 
      .value as value 
    from 
    values 
      as 
    values 
      inner join product_trials as trials ON 
    values 
      .user_id = trials.user_id 
    where 
    values 
      .source = 'Trued' 
      and 
    values 
      .use = 'true' 
      AND trials.start_date IS NOT NULL 
      AND trials.end_date IS NOT NULL 
      AND to_timestamp(
        values 
          .date / 1000
      ):: date > trials.start_date 
      AND to_timestamp(
        values 
          .date / 1000
      ):: date < trials.end_date
  ) as query1 
  inner join (
    select 
    values 
      .user_id, 
      trials.uuid as trial_uuid, 
      AVG(value) 
    from 
    values 
      inner join product_trials as trials ON 
    values 
      .user_id = trials.user_id 
    where 
      source = 'Trued' 
      and use = true 
      AND trials.start_date IS NOT NULL 
      AND trials.end_date IS NOT NULL 
      AND to_timestamp(
        values 
          .date / 1000
      ):: date < trials.start_date 
      AND DATE_PART(
        'day', 
        to_timestamp(
          values 
            .date / 1000
        ):: date - trials.start_date
      ) > -20 
    GROUP BY 
    values 
      .user_id, 
      trials.uuid
  ) as query2 ON query1.trial_uuid = query2.trial_uuid 
where 
  query2.avg > 0 
GROUP BY 
  query1.days, 
  query1.product_uuid 
ORDER BY 
  query1.product_uuid, 
  query1.days

今天关于《使用数据库中的标准差构建图表显示平均值的分布情况》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于的内容请关注golang学习网公众号！