donglao9606 2012-11-20 08:40
浏览 10
已采纳

产品统计

I do the statistic function for my project. I have the order table with some fields as:

  • prouct_id,
  • amount,
  • order_date.

The customer ask me to do the auto scale statistic as: I get the MIN(order_date) of a produc_id and the current date to calculate the days:

  • If the days about ~ 1 month ==> show the statistic of the product by weeks

  • If the days about ~ 1 year ==> show the statistic of the product by months

  • If the days >= 2 year ==> show the statistic of the product by year

I hope that can receive the ideas, examples from all of you about making the statistic as above.

  • 写回答

1条回答 默认 最新

  • douwen3965 2012-11-20 08:58
    关注

    You didn't specify what RDBMS is this. So let me assume that it is SQL Server, and you can do this:

    ;WITH MinDates AS
    (
        SELECT 
          produc_id,
          MIN(order_date) order_date
        FROM products
        GROUP BY produc_id
    ), DatesWithIntervals AS
    (
        SELECT 
          product_id,
          order_date,
          CASE 
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31      THEN 1
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365     THEN 2
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3 
            ELSE 4
          END "Interval"
        FROM MinDates
    )
    SELECT
      product_id,
      order_date
      ...
    FROM DatesWithIntervals 
    --Do your statics here
    

    You didn't specify what statistics do you want to compute. But you should be able to do whatever statistics you want to do using the last cte: DatesWithIntervals depending on the field Interval and I will leave it as a practice for you.

    Edit: For MySQL, just replace all these CTEs with subqueries like so:

    SELECT
      product_id,
      order_date
          ...
    FROM
    (
        SELECT 
          product_id,
          order_date,
          CASE 
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 31      THEN 1
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365     THEN 2
            WHEN ABS(DATEDIFF(dd, order_date, GETDATE())) < 365 * 2 THEN 3 
            ELSE 4
          END "Interval"
        FROM
        (
             SELECT 
              produc_id,
              MIN(order_date) order_date
             FROM products
             GROUP BY produc_id  
        ) MinDates
    ) DatesWithIntervals 
    --Do your statics here
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择