SQL-info信息
SQL-info信息
sql 保留两位小数+四舍五入
1. ROUND(该函数,只是负责四舍五入到两位小数,但是不负责截断 只留两位小数,例如下例:)
关于ROUND函数,我们平常理解为4舍5入,如:
print ROUND(13.145, 2);
结果为:13.150
2. 使用转换类型,才能达到保留两位小数位的目的:
select cast(13.145 as decimal(10, 2))
SQL百分比显示
- 方法一
sql&hive CONCAT(cast(t2.sales_shop_count/t1.operate_shop_count as decimal(18,4)) * 100 , '%') as `点位百分比占比`
- 方法二
CONCAT(FORMAT(lost_cost_rate * 100 ,2), '%') AS lost_cost_rate ,
环比
- 环比
本期环比增长(下降)率(%) = (本期价格/上期价格 — 1 )× 100%
union all
SELECT
'${yesterday}' AS l_date,
'环比' AS report_type,
A.city AS city,
CONCAT(FORMAT((A.gross_profit_rate / B.gross_profit_rate - 1) * 100 ,2), '%') AS gross_profit_rate,
CONCAT(FORMAT((A.pay_price / B.pay_price - 1) * 100 ,2), '%') AS pay_price
FROM (
SELECT
ca.city AS city,
(ca.pay_price - ca.cost_price) / ca.pay_price AS gross_profit_rate,
ca.pay_price AS pay_price
FROM
bi_app_public.da_city_all_daily_weekly_monthly AS ca
WHERE
ca.l_date_id = '${yesterday}'
AND ca.report_type = 'day'
) AS A
LEFT JOIN (
SELECT
ca.warehouse AS warehouse,
ca.city AS city,
(ca.pay_price - ca.cost_price) / ca.pay_price AS gross_profit_rate,
ca.pay_price AS pay_price
FROM
bi_app_public.da_city_all_daily_weekly_monthly AS ca
WHERE
ca.l_date_id in (
-- 获取上一工作日
SELECT
MAX(td.date)
FROM
bi_da.dim_time_by_day_dimension AS td
WHERE
td.date BETWEEN date_sub('${yesterday}', interval 8 day) AND date_sub('${yesterday}', interval 1 day)
AND td.is_holiday = 0
)
AND ca.report_type = 'day'
) AS B ON (A.city = B.city)
同比
- 同比
本期同比增长(下降)率(%) = (本期价格/上年同期价格 —1) × 100%
SELECT
'${yesterday}' AS l_date,
'同比' AS report_type,
A.city AS city,
CONCAT(FORMAT((A.gross_profit_rate / B.gross_profit_rate - 1) * 100 ,2), '%') AS gross_profit_rate,
CONCAT(FORMAT((A.pay_price / B.pay_price - 1) * 100 ,2), '%') AS pay_price
FROM (
SELECT
ca.city AS city,
(ca.pay_price - ca.cost_price) / ca.pay_price AS gross_profit_rate,
ca.pay_price AS pay_price
FROM
bi_app_public.da_city_all_daily_weekly_monthly AS ca
WHERE
ca.l_date_id = '${yesterday}'
AND ca.warehouse = '华东'
AND ca.report_type = 'day'
) AS A
LEFT JOIN (
SELECT
ca.city AS city,
(ca.pay_price - ca.cost_price) / ca.pay_price AS gross_profit_rate,
ca.pay_price AS pay_price
FROM
bi_app_public.da_city_all_daily_weekly_monthly AS ca
WHERE
ca.l_date_id = date_sub('${yesterday}', interval 7 day)
AND ca.report_type = 'day'
) AS B ON (A.city = B.city)
新增字段
In [ ]:
## 新增字段
alter table bi_app_huadong.da_area_all_daily add `discount_rate` decimal(16,2)
NOT NULL DEFAULT 0 COMMENT '商品折扣率' after `price_margin_rate`;
## 字段重命名
日期
mysql
- last_day(curdate());获取当月最后一天。
- DAYOFMONTH(last_day(curdate())); 返回date 对应的该月日期。当然这就是当月的天数。
- select curdate(); --获取当前日期
- select DATE_ADD(curdate(),interval -day(curdate())+1 day) --获取本月第一天
- select date_add(curdate()-day(curdate())+1,interval 1 month ) -- 获取下个月的第一天