Created
December 3, 2020 01:12
-
-
Save ufo22940268/ac5df01654c6884dc99fce17420681df to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH orders AS ( | |
SELECT review, channel, t.product._id pid, t.product.martspec, t.product.costprice, t.product.price, "create" | |
FROM (SELECT * FROM db_orders WHERE "create" BETWEEN timestamp '2020-11-01 00:00:00' AND timestamp '2020-11-30 23:59:00' AND paid = TRUE) orders | |
CROSS JOIN unnest(products) AS t(product) | |
) | |
-- 2018-03-21 12:29:05 | |
, reviews AS ( | |
SELECT r.name reviewName, c.name channelName, m._id mid, r._id rid | |
FROM db_reviews r | |
JOIN db_channels c ON r.channel = c._id | |
JOIN db_martreviews m ON r.martreview = m._id | |
) | |
, r AS (SELECT | |
martspec "sku名", | |
channelName "频道名", | |
reviewName "文章名", | |
count(*) "sku销售总数", | |
sum(costprice) "sku成本总额 (fromBD)", | |
sum(price) "sku销售总额 (fromBD)" | |
FROM orders | |
JOIN reviews ON orders.review = reviews.rid | |
GROUP BY pid, martspec, channelName, reviewName) | |
-- select sum("sku销售总额 (fromBD)"), count(*) from r where "频道名" = '爱生活的马克君' | |
SELECT * | |
FROM r where "频道名" IN('盐', '荞麦少女'); | |
-- SELECT * | |
-- FROM r where "频道名" IN('爱生活的马克君'); | |
-- select sum(price*c) from orders join reviews on orders.review = reviews.rid where channelName='爱生活的马克君' | |
-- WHERE channel='56d66433221cd2cb361c534d' | |
-- SELECT sum(price) | |
-- FROM orders | |
-- JOIN reviews ON orders.review = reviews.rid | |
-- WHERE channelName = '爱生活的马克君' | |
-- select * from db_channels where name = '爱生活的马克君'; | |
-- channel id 56d66433221cd2cb361c534d | |
-- SELECT sum(price) FROM p_orders WHERE "date" BETWEEN date '2020-04-01' and date'2020-05-01' AND paid = TRUE and channel = '56d66433221cd2cb361c534d'; | |
-- select "name" from db_channels where "name" like '%盐%' or "name" like '%荞麦%' | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment