时序数据库influxdb+grafana

第一步：grafana添加mysql数据源方法
参考：https://jingyan.baidu.com/article/b0b63dbf2eae264a483070d4.html

查询：

SELECT
  add_time as 'time',
  rate
FROM money_market
WHERE
  type = '9'
ORDER BY update_time desc

2019.8.13
进展：
既然influxdb和mysql语句都很像，那就先用mysql试验一遍，查询到最新rate和次新rate；
技术难点：
逻辑理清，mysql中max的用法；

![在这里插入图片描述](https://img-blog.csdnimg.cn/20190![在这里插入图片描述](https://img-blog.csdnimg.c
明日工作：

1. 先将mysql表中的数据导出到excel中，然后用python将其导入到influxdb并连接grafana将其可视化显示，然后执行sql查询；
2. 查询上一天最后一条rate；
3. 讨论班报告，求出截面中心点；
4. 看b站关于kdb+mysql的视频；
5. 英语口语；
6. leetcode刷题；
7. 深度学习模型；

完成1:
编写python程序，利用json解析将数据批量插入到influxdb中；
完成2:
8. SELECT * FROM test6 ORDER BY time desc limit 2;
9. SELECT * FROM test6 where time > now() - 1d ORDER BY time desc limit 2;
完成3：
触发条件查询：使用influxdb中的连续查询

create continuous query aaa on test begin select * into test8 from test6 where rate=3 group by time end 
show continuous queries

mysql中触发器查询：

CREATE TRIGGER test1
BEFORE INSERT on t1 for each row
begin 
	SELECT rate into @p from money_market WHERE rate > '2';
end;

错误1: 1415
解决：select into
错误2: 1142
解决：设置用户的trigger权限，重启连接

dophinDB

1. 安装：https://github.com/dolphindb/Tutorials_CN/blob/master/standalone_server.md
2. 将数据库写入DophinDB:`

https://github.com/dolphindb/Tutorials_CN/blob/master/import_data.md

dataFilePath = "E:\\mxy\\money.csv"
money_market = loadText(dataFilePath)

mysql查昨天最后一条数据： select rate from money_market where date(add_time) = date_sub(curdate(),interval 1 day) order by add_time limit 1;

4. 插入数据：

insert into trades values(now(),'bc',79.8,14)

最新1条数据：

select top 1* from trades where price > 10 order by time desc

次新数据：

t = select top 2 * from trades where price > 10 order by time desc
select top 1 * from t order by time

昨天最后一条数据：

select top 1* from trades where date(time) = today()-1 order by time 

今日dr大于昨日dr：

pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time desc)
select price from trades where date(time) = today() and price > pr

今日最新DR大于昨日DR，并且今日DR小于日内次新DR：

def f1(){
  new = avg(exec top 1 price from trades where price > 10 order by time desc)
  t = select top 2 * from trades where price > 10 order by time desc

  old = avg(exec top 1 price from t order by time)

  pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time)
  if(new > pr and new < old){
  	return new
	}
};
f1();

今日DR小于昨日DR：

  pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time desc)
  select price from trades where date(time) = today() and price < pr

今日次新DR小于昨日DR：

  def f2(){
    
      t = select top 2 * from trades where price > 10 order by time desc
    
      old = avg(exec top 1 price from t order by time)
    
      pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time)
      if(old<pr){
	  
      	return old
    	}
    };
    f2();

今日次新DR小于最新DR：

def f2(){
      new = avg(exec top 1 price from trades where price > 10 order by time desc)
      t = select top 2 * from trades where price > 10 order by time desc
    
      old = avg(exec top 1 price from t order by time)
    
     
      if(old<new){
	  
      	return old
    	}
    };
    f2();

今日DR与昨日DR差值大于3个bp：

def f2(){
      new = avg(exec top 1 price from trades where price > 10 order by time desc)

  pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time)
  if(new-pr>0.003){
  
  	return new
	}
};
f2();

今日DR与昨日DR差值是昨日DR的1/2：

def f2(){
      new = avg(exec top 1 price from trades where price > 10 order by time desc)
    
      pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time)
      if(new-pr==0.5*pr){
	  
      	return new
    	}
    };
f2();

今日最新DR等于昨日收盘DR：

def f2(){
      new = avg(exec top 1 price from trades where price > 10 order by time desc)
    
      pr = avg(exec top 1 price from trades where date(time) = today()-1 order by time)
      if(new==pr){
	  
      	return new
    	}
    };
f2();