188 lines
4.9 KiB
SQL
188 lines
4.9 KiB
SQL
-- 数据拆分
|
||
SELECT
|
||
m_new AS m, -- 拆分后的m字段
|
||
(if(i < k, quotient, quotient + remainder)) / 100.0 AS n, -- 金额（无尾差）
|
||
if(i > (k - t_remainder), t_div + 1, t_div) AS t -- 数量（无尾差）
|
||
FROM (
|
||
SELECT
|
||
splitByChar(',', m) AS m_array, -- 拆分m为数组
|
||
arrayEnumerate(m_array) AS i, -- 数组索引（从1开始）
|
||
n,
|
||
t,
|
||
arrayLength(m_array) AS k, -- 拆分后的记录数
|
||
toDecimal32(n * 100, 0) AS n_cents, -- 金额转分（整数）
|
||
(toDecimal32(n * 100, 0) div k) AS quotient, -- 金额商（每份基础值）
|
||
(toDecimal32(n * 100, 0) % k) AS remainder, -- 金额余数（最后一行补足）
|
||
(t div k) AS t_div, -- 数量商（每份基础值）
|
||
(t % k) AS t_remainder -- 数量余数（最后t_remainder行补足）
|
||
FROM A
|
||
)
|
||
ARRAY JOIN m_array AS m_new; -- 展开数组为多行
|
||
|
||
|
||
-- 退款运费金额是否有数据：有销才有退
|
||
SELECT COUNT()
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_return_mt
|
||
WHERE return_freight_amt > 0;
|
||
|
||
SELECT
|
||
source_system,
|
||
create_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
association_order_no,
|
||
source_platform,
|
||
platform_order_no,
|
||
return_freight_amt
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_return_mt
|
||
WHERE return_freight_amt > 0 LIMIT 1000;
|
||
|
||
-- 赠品是否有金额
|
||
SELECT COUNT()
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE is_gift = 1 AND goods_amt > 0;
|
||
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
manual_order,
|
||
is_swap_order,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE is_gift = 1 AND goods_amt > 0;
|
||
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
manual_order,
|
||
is_swap_order,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE platform_order_no = '';
|
||
|
||
|
||
-- 店铺编码对应多个名称
|
||
|
||
|
||
-- 仅退款 在调整单处理，是否有大额的就是全退的
|
||
|
||
|
||
-- 调整单和退销单的平台订单号是否和销售表一致（针对合单的）
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
manual_order,
|
||
is_swap_order,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE platform_order_no LIKE '%,%' LIMIT 1000;
|
||
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_change_mt
|
||
WHERE platform_order_no = '';
|
||
|
||
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_change_mt
|
||
WHERE platform_order_no IN (
|
||
SELECT
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE platform_order_no LIKE '%,%' LIMIT 1000
|
||
);
|
||
|
||
-- 是否换货单字段
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
manual_order,
|
||
is_swap_order,
|
||
source_platform,
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE is_swap_order IS NOT NULL LIMIT 1000;
|
||
|
||
|
||
-- 斯博兹最早下单时间
|
||
|
||
|
||
-- 线下订单和小票行数是否一致
|
||
|
||
|
||
-- 线下支付是否一对多
|
||
|
||
-- 线上2024年收入 209.6亿
|
||
-- 以钱货两清时间计算下
|
||
SELECT SUM(order_settle_amt) FROM dwd_trade_hkaudit_ecommerce_sale_mt WHERE YEAR(mgclear_date) = '2024';
|
||
|
||
SELECT SUM(return_goods_amt) FROM dwd_trade_hkaudit_ecommerce_sale_return_mt WHERE platform_order_no
|
||
IN (SELECT DISTINCT platform_order_no FROM dwd_trade_hkaudit_ecommerce_sale_mt WHERE YEAR(mgclear_date) = '2024');
|
||
|
||
SELECT SUM(special_barcode_amt) FROM dwd_trade_hkaudit_ecommerce_sale_change_mt WHERE platform_order_no
|
||
IN (SELECT DISTINCT platform_order_no FROM dwd_trade_hkaudit_ecommerce_sale_mt WHERE YEAR(mgclear_date) = '2024')
|
||
|
||
|
||
-- 线上销售均摊金额之和是否等于订单结算金额
|
||
SELECT
|
||
platform_order_no
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
GROUP BY platform_order_no HAVING SUM(goods_amt) <> MAX(order_settle_amt);
|
||
|
||
|
||
-- 销售时间调整
|
||
SELECT
|
||
COUNT()
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE dateDiff('day', toDateTime(order_date), toDateTime(deliver_date)) > 3;
|
||
|
||
SELECT
|
||
source_system,
|
||
order_date,
|
||
system_order_no,
|
||
store_code,
|
||
store_name,
|
||
manual_order,
|
||
is_swap_order,
|
||
source_platform,
|
||
platform_order_no,
|
||
pay_date,
|
||
deliver_date,
|
||
mgclear_date
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE manual_order IS NOT NULL AND dateDiff('day', toDateTime(order_date), toDateTime(deliver_date)) > 3;
|
||
|
||
SELECT
|
||
dateDiff('day', toDateTime(order_date), toDateTime(deliver_date)), COUNT(DISTINCT source_platform)
|
||
FROM dwd_trade_hkaudit_ecommerce_sale_mt
|
||
WHERE manual_order IS NULL
|
||
GROUP BY dateDiff('day', toDateTime(order_date), toDateTime(deliver_date)); |