- 查询小于某个时间
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';
- 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
- 还在尝试
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
- 新语句
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'
- 可能用到的
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)
