我有一张表记录了一些产品交付给客户的情况。 它的基本方案很简单：
| DeliveryDate | IdClient | IdProduct | Quantity | Cost |好吧，一个假设是客户一次只能购买一件商品，他们开始购买Item1，最终他们可以改变并开始购买Item2，停止购买Item1，依此类推。 这并不意味着Item1，Item2，...，ItemN始终是相同的产品，它们可能会有所不同。
因此，我想要的是能够获得为每个客户购买的不同物品，其初始购买日期以及每个客户的每个产品的顺序数量，因为我无法提前猜测将购买哪个项目。 有了这个计划：
| IdClient | IdProduct | FirstTimeDate | NumSequence |让我解释一下样例：
表：
| DeliveryDate | IdClient | IdProduct | Quantity | Cost | | 2016-05-01 | 1234 | 9876 | 2 | 1000 | | 2016-06-01 | 1234 | 9876 | 1 | 500 | | 2016-07-01 | 1234 | 8765 | 2 | 2000 | | 2016-08-01 | 1234 | 5432 | 3 | 3500 | | 2016-06-01 | 3456 | 5432 | 2 | 1500 | | 2016-07-01 | 3456 | 5432 | 1 | 700 | | 2016-08-01 | 3456 | 9523 | 2 | 2500 |期望的输出：
| IdClient | IdProduct | FirstTimeDate | NumSequence | | 1234 | 9876 | 2016-05-01 | 1 | | 1234 | 8765 | 2016-07-01 | 2 | | 1234 | 5432 | 2016-08-01 | 3 | | 3456 | 5432 | 2016-06-01 | 1 | | 3456 | 9523 | 2016-08-01 | 2 |我可以设法获得所有内容，除了顺序的数量，而不是那个，使用下面写的sql语句，我可以获得为每个客户购买的不同产品的数量：
| IdClient | IdProduct | FirstTimeDate | NumOfDistinctProducts | | 1234 | 9876 | 2016-05-01 | 3 | | 1234 | 8765 | 2016-07-01 | 3 | | 1234 | 5432 | 2016-08-01 | 3 | | 3456 | 5432 | 2016-06-01 | 2 | | 3456 | 9523 | 2016-08-01 | 2 |和sql（为简单起见，我先运行一个sql创建一个临时表，然后运行实际的查询）：
// Query to create the temporary table: CREATE TEMPORARY TABLE tmpNewDelivering AS WITH FT_CTE AS ( SELECT min(Date) ChangeDate,* FROM Deliverings WHERE Deliverings.IdProduct IN ( // Actual IdProducts // ) GROUP BY IdProduct, IdPatient ORDER BY Deliverings.Date ASC ) SELECT * from FT_CTE; // Query I want to improve: SELECT case when C.StartDate = tND.ChangeDate then "Start" else "Change" end Type, C.NumProducts NumProducts, tND.* FROM tmpNewDelivering tND INNER JOIN ( SELECT IdClient, count(IdProduct) NumProducts, min(ChangeDate) StartDate FROM tmpNewDelivering GROUP BY IdClient ) C ON tND.IdClient = C.IdClient ORDER BY tCN.Fecha DESCI have one table which records the deliverings of some products to clients. Its basic scheme is very easy:
| DeliveryDate | IdClient | IdProduct | Quantity | Cost |Well, one assumption is that the clients can only purchase one item at a time, and they begin purchasing Item1, eventually they could change and start buying Item2, stopping from buying Item1, and so on. That do not mean Item1, Item2, ..., ItemN is always the same product, they could vary.
So, what I want is being able to get the different items purchased for each costumer, with its initial date of purchase, and the number of sequence of each product for each client, since I could not guess in advance which item will be bought. With this scheme:
| IdClient | IdProduct | FirstTimeDate | NumSequence |Let me explain with a sample:
Table:
| DeliveryDate | IdClient | IdProduct | Quantity | Cost | | 2016-05-01 | 1234 | 9876 | 2 | 1000 | | 2016-06-01 | 1234 | 9876 | 1 | 500 | | 2016-07-01 | 1234 | 8765 | 2 | 2000 | | 2016-08-01 | 1234 | 5432 | 3 | 3500 | | 2016-06-01 | 3456 | 5432 | 2 | 1500 | | 2016-07-01 | 3456 | 5432 | 1 | 700 | | 2016-08-01 | 3456 | 9523 | 2 | 2500 |Desired Output:
| IdClient | IdProduct | FirstTimeDate | NumSequence | | 1234 | 9876 | 2016-05-01 | 1 | | 1234 | 8765 | 2016-07-01 | 2 | | 1234 | 5432 | 2016-08-01 | 3 | | 3456 | 5432 | 2016-06-01 | 1 | | 3456 | 9523 | 2016-08-01 | 2 |I could manage to get everything, except the number of sequence, instead of that, with the sql sentence written below I can get the number of different products purchased for each client:
| IdClient | IdProduct | FirstTimeDate | NumOfDistinctProducts | | 1234 | 9876 | 2016-05-01 | 3 | | 1234 | 8765 | 2016-07-01 | 3 | | 1234 | 5432 | 2016-08-01 | 3 | | 3456 | 5432 | 2016-06-01 | 2 | | 3456 | 9523 | 2016-08-01 | 2 |And the sql (for simplicity, I run first one sql creating a temporary table, and after that I run the actual query):
// Query to create the temporary table: CREATE TEMPORARY TABLE tmpNewDelivering AS WITH FT_CTE AS ( SELECT min(Date) ChangeDate,* FROM Deliverings WHERE Deliverings.IdProduct IN ( // Actual IdProducts // ) GROUP BY IdProduct, IdPatient ORDER BY Deliverings.Date ASC ) SELECT * from FT_CTE; // Query I want to improve: SELECT case when C.StartDate = tND.ChangeDate then "Start" else "Change" end Type, C.NumProducts NumProducts, tND.* FROM tmpNewDelivering tND INNER JOIN ( SELECT IdClient, count(IdProduct) NumProducts, min(ChangeDate) StartDate FROM tmpNewDelivering GROUP BY IdClient ) C ON tND.IdClient = C.IdClient ORDER BY tCN.Fecha DESC最满意答案
SQLite没有一些对此有用的功能。
您只需使用聚合即可获得前三列：
select IdClient, IdProduct, min(DeliveryDate) as FirstTimeDate from IdClient group by IdPatient, IdProduct;要获取序列，您可以修改此序列以获取在当前序列之前或之前购买的不同产品的数量：
| NumOfDistinctProducts
select IdClient, IdProduct, min(DeliveryDate) as FirstTimeDate, (select count(distinct d2.IdProduct) from Deliverings d2 where d2.IdClient = d.IdClient and d2.DeliveryDate <= d.DeliveryDate ) as NumSequence from Deliverings d group by IdPatient, IdProduct;注意：您可以使用临时表。 如果是这样，您可以使用临时表作为子查询。 如果您在IdClient, DeliveryDate, IdProduct上放置索引，这是一个好主意。
SQLite does not have some capabilities that would be useful for this.
You an get the first three columns just using aggregation:
select IdClient, IdProduct, min(DeliveryDate) as FirstTimeDate from IdClient group by IdPatient, IdProduct;To get the sequence, you can modify this to get the number of different products purchased on or before the current one:
| NumOfDistinctProducts
select IdClient, IdProduct, min(DeliveryDate) as FirstTimeDate, (select count(distinct d2.IdProduct) from Deliverings d2 where d2.IdClient = d.IdClient and d2.DeliveryDate <= d.DeliveryDate ) as NumSequence from Deliverings d group by IdPatient, IdProduct;Note: You can use a temporary table for this. If so, you can use the temporary table for the subquery. This is a good idea if you put an index on IdClient, DeliveryDate, IdProduct.
更多推荐
发布评论