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 ) -- 获取下个月的第一天