Navidrom 歌曲播放年度统计

navidrome-analysis.sh

#!/bin/bash

# 查询 2023 年收听最多的歌曲 / 专辑 / 歌手
# ./analysis.sh ./navidrome.db -media -year 2023
# ./analysis.sh ./navidrome.db -album -year 2023
# ./analysis.sh ./navidrome.db -artist -year 2023

# 检查参数数量
if [ "$#" -lt 2 ]; then
    echo "Usage: $0 <path_to_db_file> <-media|-album|-artist-> [-year YYYY]"
    exit 1
fi

# 赋值参数
DB_FILE=$1
shift # 移除第一个参数，后面的参数向前移动
TYPE=$1
shift # 再次移除第一个参数，后面的参数向前移动
YEAR_FILTER=""

# 检查数据库文件是否存在
if [ ! -f "$DB_FILE" ]; then
    echo "Error: Database file not found."
    exit 1
fi

# 遍历剩余的参数
while [ "$#" -gt 0 ]; do
    case "$1" in
        -year)
            shift # 移除 -year
            YEAR=$1
            YEAR_FILTER="AND strftime('%Y', ano.play_date) = '$YEAR'"
            ;;
        *)
            # 如果有未知参数，打印错误并退出
            echo "Unknown parameter: $1"
            exit 1
            ;;
    esac
    shift # 移除处理过的参数
done

echo "Analyzing the database for the year ${YEAR:-all years}..."
echo ""

# 根据参数选择 SQL 查询
case $TYPE in
    -media)
        echo "你收听得最多的歌曲"
        SQL_QUERY="SELECT 
                        SUM(ano.play_count) AS total_play_count, 
                        mf.album_artist,
                        mf.title
                FROM annotation ano
                LEFT JOIN media_file mf ON ano.item_id = mf.id
                WHERE ano.item_type = 'media_file' $YEAR_FILTER
                GROUP BY mf.title
                ORDER BY total_play_count DESC
                LIMIT 20;"

        # 执行 SQL 查询并打印结果
        printf "| %-4s | %-12s | %s\n" "排名" "次数" "名称"
        echo "--------------------------------------------------------------------------------"
        RANK=1
        sqlite3 "$DB_FILE" "$SQL_QUERY" | while read LINE; do
            IFS='|' read -ra COLS <<< "$LINE"
            printf "| %-4s | %-10s | %s  【%s】\n" "$RANK" "${COLS[0]}" "${COLS[2]}" "${COLS[1]}"
            RANK=$((RANK+1))
        done


        ;;

    -album)
        echo "你收听得最多的专辑"
        SQL_QUERY="SELECT 
                    SUM(ano.play_count) AS total_play_count,
                    album.name,
                    album.album_artist
                FROM annotation ano
                LEFT JOIN album ON ano.item_id = album.id
                WHERE ano.item_type = 'album' $YEAR_FILTER
                GROUP BY album.name, album.album_artist
                ORDER BY total_play_count DESC
                LIMIT 20;"

        # 执行 SQL 查询并打印结果
        printf "| %-4s | %-12s | %-30s \n" "排名" "次数" "名称"
        echo "--------------------------------------------------------------------------------"
        RANK=1
        sqlite3 "$DB_FILE" "$SQL_QUERY" | while read LINE; do
            IFS='|' read -ra COLS <<< "$LINE"
            printf "| %-4s | %-10s | %s  【%s】\n" "$RANK" "${COLS[0]}" "${COLS[1]}" "${COLS[2]}"
            RANK=$((RANK+1))
        done
        
        ;;

    -artist)
        echo "你收听得最多的歌手"
        SQL_QUERY="SELECT 
                        SUM(ano.play_count) AS total_play_count, 
                        mf.album_artist
                FROM annotation ano
                LEFT JOIN media_file mf ON ano.item_id = mf.id
                WHERE ano.item_type = 'media_file' $YEAR_FILTER
                GROUP BY mf.album_artist
                ORDER BY total_play_count DESC
                LIMIT 20;"

        # 执行 SQL 查询并打印结果
        printf "| %-4s | %-12s | %-30s\n" "排名" "次数" "艺术家"
        echo "------------------------------------------------"
        RANK=1
        sqlite3 "$DB_FILE" "$SQL_QUERY" | while read LINE; do
            IFS='|' read -ra COLS <<< "$LINE"
            printf "| %-4s | %-10s | %-30s\n" "$RANK" "${COLS[0]}" "${COLS[1]}"
            RANK=$((RANK+1))
        done
        ;;
    *)
        echo "Invalid type. Use -media for songs or -artist for artists."
        exit 1
        ;;
esac