3 senelik deneyimli arkadaşa yazısını görünce acaba yardımı ona göre mi yapacağız diye düşündüm önce. Sonra yok ya öyle değildir vs derken kafam karıştı. Ben de sordum. Eğer demek istediğiniz 3 senedir Laravel yazıyorum ama bir sorguda takıldım demek ise isterseniz 20 senedir bu işi yapın, insan her zaman çözüme gidemiyor, takılıyor. Sorun yok.
İstediğiniz şey için T-SQL, PostgreSQL ve MySQL 8+ de tarih aralığı oluşturup kullanmanızı sağlayacak yardımcı fonksiyonlar ve yollar var. Ayrıca gerekli tarihleri ayrı bir tabloda tutup da kullanmak da yaygın bir yöntem. Calendar table diye de geçer. Bu ve diğerlerini araştırmak okuyanlara kalsın. Onlar işin kolay kısımları ben zor olduğunu düşündüğüm MySQL <= 5.7 için versiyonunu yazayım.
Burada yapmamız gereken önce premiums (adının öyle olduğunu varsayıyorum) tablosunda ilk ve son tarihleri bulmak. Sonuçta bir tarih aralığı lazım. Bu da o tablodaki MIN(date_start) ve MAX(date_end) alanları arasında olacak. Sonra biz hangi tarihte kaç tane premium post var onu bulacağız. O yüzden tarih aralığı ile premiums tablosunu LEFT JOIN ile birşeltireceğiz. Tarih Aralığı → premiums yönünde bir join olacak (Böylece arada tarih boşluğu bırakmamış olacağız. Bu istatistiklerde ya da chart kullandığınızda çok işinize yarar. Genelde arada gap oluşur ve biz onları PHP ya da Javascript ile doldururuz):
SELECT COUNT(premiums.id) AS premium_post_count,
date_period AS date
FROM (SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) date_period
FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
LEFT JOIN premiums ON date_period >= premiums.date_start AND date_period <= premiums.date_end
WHERE date_period BETWEEN (SELECT MIN(date_start) FROM premiums) AND (SELECT MAX(date_end) FROM premiums)
GROUP BY date
ORDER BY date
Kullandığım örnek veri:
premiums
+----+---------+------------+------------+
| id | post_id | date_start | date_end |
+====+=========+============+============+
| 1 | 1 | 2020-03-02 | 2020-03-10 |
| 2 | 2 | 2020-03-02 | 2020-03-18 |
| 3 | 3 | 2020-03-12 | 2020-03-25 |
| 4 | 4 | 2020-03-18 | 2020-03-31 |
| 5 | 5 | 2020-03-02 | 2020-03-30 |
| 6 | 6 | 2020-03-11 | 2020-03-26 |
| 7 | 7 | 2020-03-02 | 2020-03-05 |
| 8 | 8 | 2020-03-05 | 2020-03-25 |
| 9 | 9 | 2020-03-06 | 2020-03-16 |
| 10 | 10 | 2020-03-01 | 2020-03-31 |
+----+---------+------------+------------+
Aldığım sonuç:
+--------------------+------------+
| premium_post_count | date |
+====================+============+
| 1 | 2020-03-01 |
| 5 | 2020-03-02 |
| 5 | 2020-03-03 |
| 5 | 2020-03-04 |
| 6 | 2020-03-05 |
| 6 | 2020-03-06 |
| 6 | 2020-03-07 |
| 6 | 2020-03-08 |
| 6 | 2020-03-09 |
| 6 | 2020-03-10 |
| 6 | 2020-03-11 |
| 7 | 2020-03-12 |
| 7 | 2020-03-13 |
| 7 | 2020-03-14 |
| 7 | 2020-03-15 |
| 7 | 2020-03-16 |
| 6 | 2020-03-17 |
| 7 | 2020-03-18 |
| 6 | 2020-03-19 |
| 6 | 2020-03-20 |
| 6 | 2020-03-21 |
| 6 | 2020-03-22 |
| 6 | 2020-03-23 |
| 6 | 2020-03-24 |
| 6 | 2020-03-25 |
| 4 | 2020-03-26 |
| 3 | 2020-03-27 |
| 3 | 2020-03-28 |
| 3 | 2020-03-29 |
| 3 | 2020-03-30 |
| 2 | 2020-03-31 |
+--------------------+------------+
Arada kullandığım için saklamıştım: Tarih aralığını oluşturan SQL. MySQL <= 5.7 kullananlar için yardımcı olacaktır ama bana göre Calendar Table kullanılması daha iyi bir çözüm gibi:
SELECT * FROM
(SELECT ADDDATE('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) date_period FROM
(SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE date_period BETWEEN '2020-03-01' AND '2020-03-31';