export_price.sql

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 '%荞麦%'