MYSQL语句

1. 查询小于某个时间

SELECT COUNT(*) FROM auth_user
WHERE date_joined <= '2017-06-04';

# 加了时区进行查询，东八区，上海，显示北京时间
SELECT count(1) FROM auth_user
WHERE (date_joined + INTERVAL 8 HOUR) <= '2017-06-05';

2. hue中日常用到的语句

获取周报---先不用

select * from t_week_mail

获取developer---先不用

SELECT
u.id,u.email,u.username,u.last_login,u.date_joined,f.mobile,f.qq,f.address
FROM
default.mysql_auth_user u
LEFT JOIN
default.mysql_gizwits_site_userprofile
f
on u.id = f.user_id

获取auth_user

SELECT * from mysql_auth_user

获取mysql_organization

select * from mysql_organization

获取gizwits_site_userprofile

select * from mysql_gizwits_site_userprofile

获取mysql_gizwits_site_member (role_id)

select * from mysql_gizwits_site_member

获取device_count

select 
p.user_id,
t.total_device as dev_count
from 
(
select 
lower(product_key) as pk,
SUM(device_count) AS total_device
from
analyzedb.t_incr_device 
group by  lower(product_key)
)  t
right join default.mysql_gizwits_site_product p
on lower(t.pk) = lower(p.product_key)

获取new_device

SELECT p.verbose_name,p.product_key,o.name as com_name,p.user_id,p.type,
d.device_count,d.created_at
from default.mysql_gizwits_site_product p
left join analyzedb.t_incr_device d
on d.product_key = p.product_key
left join default.mysql_organization o 
on p.organization_id = o.id

3. 还在尝试

SELECT p.id, p.product_key, p.verbose_name, p.is_adaptive_datapoint, d.device_count, c.product_id, o.name as organization_name
FROM default.mysql_gizwits_site_product as p LEFT JOIN default.mysql_gizwits_site_centralcontrolproduct as c
on p.id = c.product_id
LEFT JOIN analyzedb.t_incr_device as d on lower(p.product_key) = lower(d.product_key) 
LEFT JOIN default.mysql_organization as o on p.organization_id = o.id

4. 新语句
   query_result

select 
p.id, 
p.product_key,
p.verbose_name,
p.is_adaptive_datapoint,
t.total_device,
c.product_id,
o.name as organization_name
from 
(
select 
 lower(product_key) as pk,
 SUM(device_count) AS total_device
from
analyzedb.t_incr_device 
group by  lower(product_key)
)  t
right join default.mysql_gizwits_site_product p
on lower(t.pk) = lower(p.product_key)
left join default.mysql_gizwits_site_centralcontrolproduct c 
on p.id = c.product_id
left join default.mysql_organization o 
on p.organization_id = o.id

mongo_device

select m.product_key, m.is_codegen 
from default.mongo_device m
where (m.year >= 2017) and (m.month >= 6) and (m.day >= 14) and (m.is_codegen = true)

select 
    lower(product_key),
    SUM(device_count) AS total_device
from
analyzedb.t_incr_device 
group by lower(product_key)

最后汇总

select 
p.id, 
p.product_key,
p.verbose_name,
p.is_adaptive_datapoint,
t.total_device,
c.product_id,
o.name as organization_name,
mongo.product_key, mongo.is_codegen
from 
(
select 
lower(product_key) as pk,
SUM(device_count) AS total_device
from
analyzedb.t_incr_device 
group by  lower(product_key)
)  t
right join default.mysql_gizwits_site_product p
on lower(t.pk) = lower(p.product_key)
left join default.mysql_gizwits_site_centralcontrolproduct c 
on p.id = c.product_id
left join default.mysql_organization o 
on p.organization_id = o.id
left join (select m.product_key, m.is_codegen 
from default.mongo_device m
where (m.year >= 2017) and (m.month >= 6) and (m.day >= 14) and (m.is_codegen = true)) mongo
on lower(mongo.product_key) = lower(p.product_key)

会不会忘记加distinct

select count(mac) from mongo_device where year = 2017 and month=6 and is_codegen=true and default.mac2type(mac)='NORMAL_MAC'

5. 可能用到的

SELECT * from superset_retention
order by time DESC
limit 3

like

select * from mysql_gizwits_site_product where verbose_name like '%Allpay%'

select * from mysql_organization where name like '%奥付云%'

奥付云（AllpayV2_1正式平台）的设备累计数

select * from analyzedb.t_incr_device where product_key = '41755b79b566447d9b217c20bfaac91f'

select  
sum(device_count) 
from   analyzedb.t_incr_device  
where created_at<20170301  and   lower(product_key)="41755b79b566447d9b217c20bfaac91f"

select
     incr.created_at as created_at,
     SUM(incr.count) OVER (ORDER BY  incr.created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS device_count
   from (
     select  
    created_at ,
     CASE  WHEN created_at=20170301  then sum(device_count)+8250  else sum(device_count)   end  as count
    from   analyzedb.t_incr_device  
    where created_at>=20170301  and   lower(product_key)="41755b79b566447d9b217c20bfaac91f"  group by   created_at
   )  incr

bumblebee

SELECT * FROM `device_settings` where device_id in 
(select device_id from device_settings GROUP BY device_id HAVING count(device_id) > 1);

---

---

获取user_id 和 dev_count

select 
p.user_id,
t.total_device as dev_count
from 
(
select 
lower(product_key) as pk,
SUM(device_count) AS total_device
from
analyzedb.t_incr_device 
group by  lower(product_key)
)  t
right join default.mysql_gizwits_site_product p
on lower(t.pk) = lower(p.product_key)