user430997's questions

我有一个表（sometable）中有一列（somecolumn），其格式如下（DATE）：2025-01-01 00:00:00

使用 SQL，我想要创建以下两列：

1）根据四月-四月日历提取月份数字，即四月=1，五月=2，......

2）按照四月-四月日历确定年份，即2025年1月1日属于2024-2025年。

第一个很容易手动完成：

SELECT 
    CASE 
        WHEN EXTRACT(MONTH FROM somedate) = 4 THEN 1
        WHEN EXTRACT(MONTH FROM somedate) = 5 THEN 2
        WHEN EXTRACT(MONTH FROM somedate) = 6 THEN 3
        WHEN EXTRACT(MONTH FROM somedate) = 7 THEN 4
        WHEN EXTRACT(MONTH FROM somedate) = 8 THEN 5
        WHEN EXTRACT(MONTH FROM somedate) = 9 THEN 6
        WHEN EXTRACT(MONTH FROM somedate) = 10 THEN 7
        WHEN EXTRACT(MONTH FROM somedate) = 11 THEN 8
        WHEN EXTRACT(MONTH FROM somedate) = 12 THEN 9
        WHEN EXTRACT(MONTH FROM somedate) = 1 THEN 10
        WHEN EXTRACT(MONTH FROM somedate) = 2 THEN 11
        WHEN EXTRACT(MONTH FROM somedate) = 3 THEN 12
    END AS new_month
FROM sometable;

第二个更令人困惑。我尝试通过将月份分为（一月至三月等）来思考其中的逻辑：

    CASE 
        WHEN EXTRACT(MONTH FROM somecolumn) <= 3 
        THEN (EXTRACT(YEAR FROM somecolumn) - 1) || '-' || EXTRACT(YEAR FROM somecolumn)
        ELSE EXTRACT(YEAR FROM somecolumn) || '-' || (EXTRACT(YEAR FROM somecolumn) + 1)
    END AS new_year
FROM sometable;

这是正确的逻辑吗？

我在 R 中有这张图，其中每个节点都有一个随机权重：

library(igraph)
library(tidyverse)
set.seed(123)

g <- sample_gnm(n = 20, m = 30, directed = FALSE)
V(g)$weight <- runif(20)

我试图找出哪个节点的权重最接近所有节点的平均值。

如果这不是网络图，那么这应该很容易做到。但是，我不确定如何使用网络图对象来实现这一点。

我尝试使用以下方式来做到这一点：

tibble(
    node_id = 1:20,
    weight = V(g)$weight,
    mean_weight_of_all_nodes = mean(V(g)$weight),
    abs_distance_from_mean = abs(weight - mean_weight_of_all_nodes)
) %>%
    arrange(abs_distance_from_mean) %>%
    mutate(rank = row_number())

结果如下：

# A tibble: 20 × 5
   node_id weight mean_weight_of_all_nodes abs_distance_from_mean  rank
     <int>  <dbl>                    <dbl>                  <dbl> <int>
 1      12 0.414                     0.396                 0.0180     1
 2      11 0.415                     0.396                 0.0188     2
 3      13 0.369                     0.396                 0.0269     3
 4      17 0.466                     0.396                 0.0702     4
 5       8 0.318                     0.396                 0.0776     5
 6       5 0.478                     0.396                 0.0820     6
 7      18 0.266                     0.396                 0.130      7
 8      16 0.233                     0.396                 0.163      8
 9       9 0.232                     0.396                 0.164      9
10       7 0.216                     0.396                 0.179     10
11      14 0.152                     0.396                 0.243     11
12      10 0.143                     0.396                 0.253     12
13      15 0.139                     0.396                 0.257     13
14       2 0.691                     0.396                 0.295     14
15      20 0.0458                    0.396                 0.350     15
16       6 0.758                     0.396                 0.363     16
17       4 0.0246                    0.396                 0.371     17
18       3 0.795                     0.396                 0.400     18
19      19 0.858                     0.396                 0.462     19
20       1 0.902                     0.396                 0.507     20

这是正确的方法吗？

这是我正在处理的问题。

我从 R 中的时间序列模型获得了以下两个输出：

library(forecast)
library(dplyr)
set.seed(123)

dates <- seq(as.Date("2000-01-01"), by = "month", length.out = 100)
##################


values1 <- rnorm(100, mean = 100, sd = 100)
ts_data1 <- ts(values1, start = c(2000, 1), frequency = 12)
model1 <- auto.arima(ts_data1)

forecast_data1 <- forecast(model1, h = 12)
forecast_data1$class = 1

####################


values2 <- rnorm(100, mean = 200, sd = 100)
ts_data2 <- ts(values2, start = c(2000, 1), frequency = 12)
model2 <- auto.arima(ts_data2)

forecast_data2 <- forecast(model2, h = 12)
forecast_data2$class = 2

我正在尝试将这两个结合在一起，例如

 rbind(data.frame(forecast_data1), data.frame(forecast_data2))
          Point.Forecast        Lo.80    Hi.80       Lo.95    Hi.95
May 2008       116.88772   2.03451679 231.7409  -58.765094 292.5405
Jun 2008        84.77524 -30.07795384 199.6284  -90.877564 260.4281
Jul 2008        88.15699 -26.69621184 203.0102  -87.495822 263.8098
Aug 2008        97.84920 -17.00400178 212.7024  -77.803612 273.5020
Sep 2008       104.59216 -10.26103639 219.4454  -71.060647 280.2450
Oct 2008       123.46321   8.61001633 238.3164  -52.189594 299.1160
Nov 2008        80.16232 -34.69087853 195.0155  -95.490489 255.8151
Dec 2008       122.86121   8.00801357 237.7144  -52.791597 298.5140
Jan 2009        62.16134 -52.69186110 177.0145 -113.491472 237.8141
Feb 2009        76.41793 -38.43527070 191.2711  -99.234881 252.0707
Mar 2009       114.92293   0.06973034 229.7761  -60.729880 290.5757
Apr 2009       132.14088  17.28768324 246.9941  -43.511927 307.7937
May 20081      173.02664  50.54760673 295.5057  -14.288876 360.3422
Jun 20081      196.17783  73.69880321 318.6569    8.862321 383.4933
Jul 20081      181.38489  58.90585584 303.8639   -5.930626 368.7004
Aug 20081      196.21547  73.73644398 318.6945    8.899962 383.5310
Sep 20081      189.46162  66.98258965 311.9407    2.146107 376.7771
Oct 20081      200.14378  77.66475146 322.6228   12.828269 387.4593
Nov 20081      214.71057  92.23154347 337.1896   27.395061 402.0261
Dec 20081      151.90221  29.42317702 274.3812  -35.413305 339.2177
Jan 20091      176.64285  54.16381860 299.1219  -10.672664 363.9584
Feb 20091      211.48637  89.00734228 333.9654   24.170860 398.8019
Mar 20091      196.08048  73.60144798 318.5595    8.764966 383.3960
Apr 20091      210.85820  88.37916960 333.3372   23.542687 398.1737

但由于某种原因，类变量（即 1 或 2）没有显示出来以表明预测来自哪个模型。

我该如何修复此问题？

我在 R 中有以下数据：

library(ggplot2)
library(dplyr)

set.seed(123)

data <- data.frame(
    height = rnorm(1000, mean = 170, sd = 10),
    weight = rnorm(1000, mean = 70, sd = 15),
    country = sample(c("USA", "Canada", "UK", "Australia"), 1000, replace = TRUE)
)

我使用这段代码来为每个国家/地区制作身高与体重的方面：

library(ggplot2)
library(gridExtra)

# Create separate plots for each country
p1 <- ggplot(data %>% filter(country == "USA"), aes(x = height, y = weight)) +
    geom_point(alpha = 0.5) +
    labs(title = "USA", x = "Height (cm)", y = "Weight (kg)") +
    theme_minimal()

p2 <- ggplot(data %>% filter(country == "Canada"), aes(x = height, y = weight)) +
    geom_point(alpha = 0.5) +
    labs(title = "Canada", x = "Height (cm)", y = "Weight (kg)") +
    theme_minimal()

p3 <- ggplot(data %>% filter(country == "UK"), aes(x = height, y = weight)) +
    geom_point(alpha = 0.5) +
    labs(title = "UK", x = "Height (cm)", y = "Weight (kg)") +
    theme_minimal()

p4 <- ggplot(data %>% filter(country == "Australia"), aes(x = height, y = weight)) +
    geom_point(alpha = 0.5) +
    labs(title = "Australia", x = "Height (cm)", y = "Weight (kg)") +
    theme_minimal()



grid.arrange(p1, p2, p3, p4, ncol = 2)

R 中是否有一些功能可以让我不用手动制作所有这些图表，而是可以自动制作具有正确标题的每个图表并将它们平铺在同一页面上？

现在我正在手动执行此操作，这需要一些时间（例如，我有 20 个这样的图表） - 难道不能自动执行此操作吗？

我在 R 中有一个如下所示的数据集：

data = structure(list(quarter = c("Q1 2005", "Q2 2005", "Q3 2005", "Q4 2005", 
"Q1 2006"), value = c(128.76, 178.83, 140.9, 188.3, 194.05)), class = "data.frame", row.names = c(NA, 
-5L))

我想扩展这个数据集，添加一个名为“月份”的额外列（例如一月、二月、三月……）...并且我想将差额分割到连续季度之间的所有月份，以便各个季度之间的数字仍然相加。

我尝试这样做：

library(dplyr)

expand_dataset <- function(data) {
quarter_to_months <- list(
    "Q1" = c("Jan", "Feb", "Mar"),
    "Q2" = c("Apr", "May", "Jun"),
    "Q3" = c("Jul", "Aug", "Sep"),
    "Q4" = c("Oct", "Nov", "Dec")
    )

expanded_data <- data.frame()

for (i in 1:(nrow(data) - 1)) {
    current_value <- data$value[i]
    next_value <- data$value[i + 1]
    
    diff <- (next_value - current_value) / 3
    
    quarter <- substr(data$quarter[i], 1, 2)
    year <- substr(data$quarter[i], 4, 7)
    months <- quarter_to_months[[quarter]]
    
    for (j in 1:3) {
        month_value <- current_value + (j - 1) * diff
        expanded_data <- rbind(expanded_data, data.frame(
            quarter = data$quarter[i],
            month = months[j],
            year = year,
            value = month_value
        ))
    }
    }

return(expanded_data)
}

expanded_data <- expand_dataset(data)
print(expanded_data)

这是正确的做法吗？有没有更简单的方法？