开发者学堂课程【新电商大数据平台2020最新课程：电商项目之交易订单明细流水表 SQL 实现（上）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/640/detail/10533
电商项目之交易订单明细流水表 SQL 实现（上）
因为我们在做的时候不光要会写 sql ，还要会分析，所以上节课我们做了一下右分析。这节课我们针对上一节课所做的右分析，进行一个 sql 实现。如此的话 ，会把图中的两列信息全部打开，到时候会一一对应的去查询数据。
打开笔记，创建对应的交易主题，里面是交易订单明细流水表。
完成后，我们该如何去实现呢？
首先，看一下我们的表数据。比如说，每个表里面都有对应的一个字段，在这里提示的一共有四个表，是我们需要获取的。那现在就按照我的一个 sql 来给大家写一下。
先把 hive 启动一下，打上 hive 。启动之后就和它没关系了。
首先，创建一个拟真表；在这里面写 sql 。
with tborder as (
select
)
因为我们要分几个批次去做，所以第一步，我们要先获取 order_id ,包括我们的 order 的用户、订单状态以及用户的信息。接着查询我们的订单表，把订单表的里面数据能获取到的全部获取到。
from 我们的订单表，复制粘贴，检查好不敢出错。
with tborder as (
select
from ods_nshop.ods_02_orders
)
订单信息填入好后，我们要输入条件，考虑这个表是不是要全部导入订单表的信息。它没有按照我们的时间划分进行分居，所以我们在获取订单数据的时候需要判断当前这条记录是否是当天的数据，因为是全部导入，它可能会存在历史记录。
假如此时想分析2020年3月30日的一个数据结果或者是订单状态，这个时候我们需要看一下 order_ctime 创建时间是什么时候？
假如说创建时间是今天（2020年3月30日），不管是十点还是十一点，就都是我的订单。如果说是昨天、前天、大前天或者前七天的，就不是我的订单。
所以说，第一步我们首先要判断条件，即今天的订单信息必须是我的数据，就需要查询 order ctime ，输入 hive> select * from ods_nshop.ods_02_orders limit 10;可以看到是跳转页面后的倒数第三个。
这里需要用实际转换给它转换一下，我们是只需要年月日的， bigint 的后面有个逗号，后面等于这个订单创建的时间，这个时间我们需要在转一下，可以看到日期是11.02。就等于20191102，不然会没有数据。
with tborder as (
select
from ods_nshop.ods_02_orders
where
from_unixtime(cast(order_ctime/1000 as bigint),‘yyyyMMdd’)=‘20191102’
)
把条件先摆在这里，接下来要获取 order_id 。可以看到订单表里 order_id 、 customer_id 都有，就是说我们可以获取用户的订单 ID 和用户的订单表，也就是说可以获取 customer_id 用户订单 ID 、订单状态等。
看交易订单明细流水表中，是需要用户 ID 和订单 ID ，当中的用户 ID 就是订单表中的 customer_id 用户订单 ID ，也能获取到订单状态。
这里先获取 order_id 、 customer_id 还有状态、收货人地址、支付类型。然后，输入 consignee_zipcode 、 pay_type 。 order_ctime 也能获取，把它拿过来是因为我们后面会用到，使用 order_ctime 和其他的表里的数据进行对比，比如说，订单详情表、交易记录详情表、支付记录详情表等等。
with tborder as (
select
order_id,
customer_id,
order_status,
consignee_zipcode,
pay_type,
order_ctime,
from ods_nshop.ods_02_orders
where
from_unixtime(cast(order_ctime/1000 as bigint),‘yyyyMMdd’)=‘20191102’
)
之后我们获取当前时间。因为它里面没有分居时间，所以先不获取。上述内容就是我们能获取到的已知数据。
在最下面的括号后打上逗号，再写一个流水表。 from？接下来要找到订单详情表、商品记录表，由商品信息表取到售卖地即可。此时需要用到订单的详情表，因为要用订单详情表里面的信息和商品信息表里面对应的去照应、取值。
取值大家要注意，需要用到 product_id 和 product_code 进行照应，因为 product_id 要在订单商品 ID 和商品信息的商品 ID 进行照应。
在 join 完成之后，需要注意的是：我们要取到商品信息表里的商品供应商编码和商品的销售价格，因为在交易订单明细流水表中需要用到。在交易订单明细流水表中有一个店铺 ID ，供应商是属于店铺的，所以会用到供应商编码。
商品的销售价格也需要用到，因为后面需要进行计算，我们先取到这两个信息。剩余其他的在订单详情表里都可以取到相关的信息，比如说：
订单 ID 、商品 ID 、购买商品数量、购买商品单价、商品加权价格、优惠金额。对于商品单价，要以商品信息表里的商品单价为准。
不能在订单表中获取上述信息，因为订单表只是下单状态，是否参与优惠、有无优惠的活动都只能通过订单详情表来判断。
订单表只是先获取用户的购买订单信息，只有订单详情表才会记录真实的订单记录信息。
检验我们的信息是否在一个库当中？输入 show databases 检验。
我们可以看到都是 ods 没有dim 。
这里接着写下一个 sql 。给 ods_nshop.ods_02_order_detail 命名叫 a 。打开商品信息表，给 ods_nshop.dim_pub_product 命名叫 b 。
后面on a.product_id ，在订单详情表中可以看到订单商品 id ，和商品信息表中的商品 id 进行照应，
这两个表都不是分居表，所以有一个 where 条件，有一个时间，这里我们用上一个表的就可以，将里面的进行修改，修改为订单详情表里的下单时间。完成之后进行取值，取能获取的已知信息，比如： order_id 。因为我们所做的这几个表都需要照应，所以要有照应条件。
cnt 是订单详情表中的商品购买数量，因为最终的交易订单明细流水表中需要得到购买商品数量，这里需要注意。还取了商品加权价格、优惠金额、是否参加活动，到后面需要判断参加优惠的活动。之后取 b ， b 取供应商的 id ，即商品的供应商编码，还取商品的销售价格。时间不取，就完成了。
tbdetail_as(
select
a.order_id,
a.product_id,
a.product_cnt,
a.weighing_cost,
a.district_money,
a.is_activity,
b.supplier_code,
b.product_price,
from ods_nshop.ods_02_order_detail a
join ods_nshop.dim_pub_product b
on a.product_id =b.product_code
where
from_unixtime(cast(a.order_ctime/1000 as bigint),‘yyyyMMdd’)=‘20191102’
)
