MySQL select рассчитывать только новые идентификаторы для каждого года
У вас есть как минимум пара вариантов. Первый довольно простой, общий (в том, что он не ограничивается прямыми сегментами) и точный, но использует base plot, а не ggplot. Второй использует ggplot, но немного сложнее, и переход цвета не будет на 100% точным (но достаточно близко, если вы укажете соответствующее разрешение ... читайте дальше).
base :
Если вы хотите использовать функции построения base, а не ggplot, вы можете обрезать область построения над порогом (2.2), а затем нарисуйте сегменты в предпочтительном цвете и затем скопируйте в область ниже порога и снова зарисуйте красным цветом. [1]
[/g0]
ggplot:
[/g0]
ggplot:
] Если вам нужно или нужно использовать ggplot, вы можете рассмотреть следующее ...
Одним из решений является использование geom_line(aes(group=id, color = y < 2.2)), однако это назначит цвета на основе значения y точки в начале каждого сегмента. Я считаю, что вы хотите изменить цвет не только на узлах, но и везде, где линия пересекает ваш заданный порог 2,2. Я не так хорошо знаком с ggplot, но один из способов добиться этого - сделать версию данных с более высоким разрешением, создав новые точки вдоль линий, которые соединяют ваши существующие точки, а затем используйте аргумент color = y < 2.2 для достижения желаемый эффект.
Например:
threshold <- 2.2 # set colour-transition threshold
yres <- 0.01 # y-resolution (accuracy of colour change location)
d <- stackOne # for code simplification
# new cols for point coordinates of line end
d$y2 <- c(d$y[-1], NA)
d$x2 <- c(d$x[-1], NA)
d <- d[-findInterval(unique(d$id), d$id), ] # remove last row for each group
# new high-resolution y coordinates between each pair within each group
y.new <- apply(d, 1, function(x) {
seq(x['y'], x['y2'], yres*sign(x['y2'] - x['y']))
})
d$len <- sapply(y.new, length) # length of each series of points
# new high-resolution x coordinates corresponding with new y-coords
x.new <- apply(d, 1, function(x) {
seq(x['x'], x['x2'], length.out=x['len'])
})
id <- rep(seq_along(y.new), d$len) # new group id vector
y.new <- unlist(y.new)
x.new <- unlist(x.new)
d.new <- data.frame(id=id, x=x.new, y=y.new)
p <- ggplot(d.new, aes(x=x,y=y)) +
geom_line(aes(group=d.new$id, color=d.new$y < threshold))+
geom_point(data=stackOne)+
scale_color_discrete(sprintf('Below %s', threshold))
p
[/g1]
Возможно, есть способ сделать это через функции ggplot , но тем временем я надеюсь, что это поможет. Я не мог понять, как нарисовать ggplotGrob в обрезанной viewport (скорее, это просто масштабирует сюжет). Если вы хотите, чтобы цвет был обусловлен некоторым порогом x-значения, это, очевидно, потребовало бы некоторой настройки.
3 ответа
Вы можете использовать подзапрос, чтобы получить первый год каждого client_id, сгруппированного по client_id, а затем подсчитать вхождение client_id, сгруппированного по году, так:
SELECT COUNT(client_id), YEAR_MIN FROM (
SELECT client_id, MIN(YEAR(date)) AS YEAR_MIN
FROM table
GROUP BY client_id) AS T
GROUP BY YEAR_MIN
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,client_id INT NOT NULL
,date INT NOT NULL
);
INSERT INTO my_table VALUES
(1,12,2008),
(2,15,2008),
(3,23,2008),
(4,12,2009),
(5,12,2009),
(6,18,2009),
(7,23,2010),
(8,18,2010),
(9,21,2010);
SELECT year
, COUNT(*) total
FROM
( SELECT client_id, MIN(date) year FROM my_table GROUP BY client_id ) x
GROUP
BY year;
+------+-------+
| year | total |
+------+-------+
| 2008 | 3 |
| 2009 | 1 |
| 2010 | 1 |
+------+-------+
Итак, вы хотите сосчитать первую дату, когда клиент появляется в таблице. Другими словами, строка, для которой не существует другой строки с более ранней датой и тем же клиентом. Вы можете сделать это с помощью исключения .
Тогда вы можете считать их в год, как вы делаете сейчас.
SELECT YEAR(t.date) AS yr, COUNT(t.client_id) AS client_count
FROM (
SELECT t1.client_id, t1.date
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON (t1.client_id=t2.client_id AND t1.date > t2.date)
WHERE t2.client_id IS NULL) AS t
GROUP BY yr
Вы должны хранить даты, используя тип данных DATE, который использует формат ГГГГ-ММ-ДД. Вы не сможете выполнять > сравнения, если ваши даты хранятся в виде строк в формате DD-MM-YYYY.