SQL先进先出忠诚度积分 [英] SQL First In First Out Loyalty Point

问题描述

资深开发人员和分析师.我有一些SQL方面的经验，并曾诉诸类似的职位.但是，这是一个小众市场.预先感谢您的帮助.

fellow developers and analysts. I have some experience in SQL and have resorted to similar posts. However, this is slightly more niche. Thank you in advance for helping.

我有以下数据集(经过编辑的道歉)

I have the below dataset (edited. Apology)

设置

CREATE TABLE CustomerPoints
(
    CustomerID INT,
    [Date]     Date,
    Points     INT
)

INSERT INTO CustomerPoints
VALUES
    (1, '20150101', 500),
    (1, '20150201', -400),
    (1, '20151101', 300),
    (1, '20151201', -400)

，并且需要将其变成(已编辑.上表中的数字不正确)

and need to turn it into (edited. The figures in previous table were incorrect)

任何正数的积分都是所赚取的积分，而负数的则兑换为积分.由于先进先出(先进先出概念)，在第二批花费的积分(-400)中，其中100积分是从20150101(英国格式)获得的积分中抽取的，而2015 20151则是300.

Any positive amount of points are points earned whereas negative are redeemed. Because of the FIFO (1st in 1st out concept), of the second batch of points spent (-400), 100 of those were taken from points earned on 20150101 (UK format) and 300 from 20151101.

目标是为每个客户计算在赚取的x和y个月内花费的积分数量.再次感谢您的帮助.

The goal is to calculate, for each customer, the number of points spent within x and y months of earning. Again, thank you for your help.

推荐答案

我已经回答了类似的问题此处

I have already answered a similar question here and here

您需要分解单个单位赚取和兑换的积分，然后将它们配对，以便每个赚取的积分都将与兑换的积分相匹配.

You need to explode points earned and redeemed by single units and then couple them, so each point earned will be matched by a redeemed point.

对于这些匹配的行中的每行，计算从赚取到兑换所用的月数，然后将其全部汇总.

For each of these matching rows calculate the months elapsed from the earning to the redeeming and then aggregate it all.

对于FN_NUMBERS(n)这是一个理货单，请查看我上面链接的其他答案.

For FN_NUMBERS(n) it is a tally table, look at other answers I have linked above.

;with
p as (select * from CustomerPoints),
e as (select * from p where points>0),
r as (select * from p where points<0),
ex as (
    select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn
    from e
    join FN_NUMBERS(1000) on N<= e.points
),
rx as (
    select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn
    from r
    join FN_NUMBERS(1000) on N<= -r.points
),
j as (
select ex.CustomerID, DATEDIFF(month,ex.date, rx.date) mm
from ex
join rx on ex.CustomerID = rx.CustomerID and ex.rn = rx.rn and rx.date>ex.date
)
-- use this select to see points redeemed in current and past semester
select * from j  join (select 0 s union all select 1 s ) p on j.mm >= (p.s*6)+(p.s) and j.mm < p.s*6+6 pivot (count(mm) for s in ([0],[2])) p order by 1, 2

-- use this select to see points redeemed with months detail
--select * from j pivot (count(mm) for mm in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p order by 1

-- use this select to see points redeemed in rows per month
--select CustomerID, mm, COUNT(mm) PointsRedeemed   from j  group by CustomerID, mm order by 1

默认查询的输出，0为0-6个月，1为7-12(以月为单位的赎回年龄)

output of default query, 0 is 0-6 months, 1 is 7-12 (age of redemption in months)

CustomerID  0   1
1           700 100

第二个查询的输出，0..12是兑换的月份数

output of 2nd query, 0..12 is the age of redemption in months

CustomerID  0   1   2   3   4   5   6   7   8   9   10  11  12
1           0   700 0   0   0   0   0   0   0   0   0   100 0

第三个查询的输出，是赎回的时间(以月为单位)

output from 3rd query, is the age of redemption in months

CustomerID  mm  PointsRedeemed
1           1   700
1           11  100

再见

这篇关于SQL先进先出忠诚度积分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！