47.Merchant - 生成上月销售报告

索尼娅,dimestore总经理,要求她的销售人员上月销售报告。

鲁迪运行N1QL查询生成报告所需的数据。试一下。

SELECT SUBSTR(purchases.purchasedAt, 0, 7) as month, 
    ROUND(SUM(product.unitPrice * items.count)/1000000, 3) revenueMillion
FROM purchases UNNEST purchases.lineItems AS items JOIN product ON KEYS items.product 
GROUP BY SUBSTR(purchases.purchasedAt, 0, 7) 
ORDER BY month

结果:

{
  "results": [
    {
      "month": "2013-05",
      "revenueMillion": 0.611
    },
    {
      "month": "2013-06",
      "revenueMillion": 0.68
    },
    {
      "month": "2013-07",
      "revenueMillion": 0.601
    },
    {
      "month": "2013-08",
      "revenueMillion": 0.596
    },
    {
      "month": "2013-09",
      "revenueMillion": 0.554
    },
    {
      "month": "2013-10",
      "revenueMillion": 0.617
    },
    {
      "month": "2013-11",
      "revenueMillion": 0.595
    },
    {
      "month": "2013-12",
      "revenueMillion": 0.596
    },
    {
      "month": "2014-01",
      "revenueMillion": 0.613
    },
    {
      "month": "2014-02",
      "revenueMillion": 0.587
    },
    {
      "month": "2014-03",
      "revenueMillion": 0.624
    },
    {
      "month": "2014-04",
      "revenueMillion": 0.602
    },
    {
      "month": "2014-05",
      "revenueMillion": 0.628
    }
  ]
}